How to Use VLOOKUP Right to Left (with IF & CHOOSE Functions)
Prologue
In this article, you’ll learn how to use the VLOOKUP function to look up values vertically from the right side to the left side. This is also known as the reverse VLOOKUP.
Excel will display a N/A error if you attempt to use the VLOOKUP function to match data on the right side and retrieve data from the left side of the matched data. The following image shows the error.
So, you’ll need help from other functions. There will be two formulas to accomplish this; the first will combine the IF function, and the second will merge the CHOOSE function with the VLOOKUP function.
VLOOKUP Function Summary
The VLOOKUP function is used to lookup values vertically. The syntax of this function is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) BLOCK FORMATTING
- lookup_value: the value you want to match
- table_array: the location to search for the value
- col_index_num: the column that contains the value
- [range_lookup]: sets the match for the value approximately or exactly.
The last parameter is optional. The default value is True (an approximate match). Where False means an exact match.
You can also use 0 for an exact match and 1 for an approximate match.
Additionally, this function is case insensitive. So, EXCELFLASH or excelflash will be equal in this formula.
Use Cases of VLOOKUP Right to Left
- Some users may find it difficult to learn new functions. VLOOKUP, IF, and CHOOSE functions are fairly common.
- If you’re already using the VLOOKUP function in the worksheet, using more will ensure consistency.
- The simple XLOOKUP function is only available in the most recent Excel versions, and INDEX-MATCH is difficult to use. So, using VLOOKUP will ensure compatibility and ease of use.
- For users who want to learn alternative methods and in- depth understanding of Excel features, this article will be useful.
Formula I: VLOOKUP Right to Left with IF Function
This is the first formula to lookup right to left using VLOOKUP function.
=VLOOKUP(B12,IF({1,0},$C$3:$C$8,$B$3:$B$8),2,0)
Decoding Formula I
- IF({1,0},$C$3:$C$8,$B$3:$B$8)
- {1,0} means array. 1 is true, and 0 is false.
- When true, the C3:C8 range is returned; when false, the B3:B8 range is returned.
- The condition, however, is not specified. As a result, the formula will return an array with the first element from the C range and the second element from the B range.
- Ultimately, it will return the table_array part of the VLOOKUP formula, where the first column is Name.
- After that, the formula will turn into a regular VLOOKUP formula, and the ID numbers will be extracted from the names.
Formula II: VLOOKUP Right to Left with CHOOSE Function
Now, let’s see another formula to do the same.
=VLOOKUP(B12,CHOOSE({1,2},$C$3:$C$8,$B$3:$B$8),2,0)
Decoding Formula II
- The CHOOSE Function returns a value from a range. The user fixes the position.
- CHOOSE({1,2},$C$3:$C$8,$B$3:$B$8)
- {1,2} means array. 1 is the first position, and 2 is the second.
- For the first element, the C3:C8 range is returned; the B3:B8 range is returned for the later.
- The position, however, is not specified. As a result, the formula will return an array with the first element from the C range and the second element from the B range.
- Ultimately, it will return the table_array part of the VLOOKUP formula, where the first column is Name.
- After that, the formula will turn into a regular VLOOKUP formula, and the ID numbers will be extracted from the names.
Facts About VLOOKUP Right to Left
- Remember to use absolute cell referencing in the formula. Otherwise, it will not work when dragging.
- In the VLOOKUP formula, 0 denotes an exact match.
Download Section
You can download the Excel file from below.
Epilogue
You’ve learned about how to use VLOOKUP from right to left using the IF and CHOOSE functions. Additionally, If you’ve got anything to share, please feel free to comment below.
Furthermore, you can also follow Excel Flash on Twitter and YouTube.