5 Ways to Vlookup When Lookup Value Is Not in the First Column
Generally, we use the VLOOKUP function to look up a certain value vertically across columns. One of the major conditions of using the VLOOKUP function is that the lookup value should belong to the first column of the table array. If this condition is not met properly, it returns the #N/A error. In such cases either you have to tweak the table array or just alter the lookup column. But in the case of large data tables, it might be impractical. Thus, I’m going to share 5 ways to lookup vertically when the lookup value is not in the first column of the table array.
VLOOKUP Function Returns #N/A When Lookup Value Is Not in the First Column
In the following example,
- The lookup value is Trevor Rios in cell F4.
- The table array is $A$2:$C$10.
Here, the lookup value is located in the second column of the table array. As a result, the VLOOKUP function throws the #N/A error in cell F5.
The VLOOKUP function is programmed in such a way that it always looks for the lookup value across the first column of the table array.
In the first column of the table array $A$2:$C$10, there’s no such record as Trevor Rios but employee ids. Thus, the VLOOKUP function can’t retrieve any data. So it leaves a #N/A error.
If you click on the error icon, it displays ‘A value is not available to the formula or function.’.
Odds of Using VLOOKUP Function
The VLOOKUP function has some limitations. They are:
- Either the output column range or its column index number must be specified in the formula. This becomes incredibly challenging when the data table has a large number of columns. Though the column count can be managed using another function.
- It pre-assumes that the lookup column is letterwise sorted.
- It creates a static formula. This means that after applying the VLOOKUP function, a change in the source data doesn’t update the output. Which is depressing in some cases.
- When the lookup column is not sorted, the VLOOKUP function may fail to extract the intended output in some cases.
Alternatives to the VLOOKUP Function
If you have issues with the limitations of the VLOOKUP function stated above, you can move on to the INDEX & MATCH functions. These two functions in collaboration provide a lot of flexibility over the VLOOKUP function.
But, if the limitations are not that important, you can definitely use the VLOOKUP function.
Best Way to Vlookup When Lookup Value Is Not in the First Column
Instead of using the VLOOKUP function, you can use the INDEX & MATCH functions to look up vertically across columns.
It doesn’t require to be the lookup value in the first column of the lookup array.
All you need to do is, insert the output array inside the INDEX function. Then insert the lookup value, lookup array, and the match type inside the MATCH function.
Formula Syntax
=INDEX(array or reference, MATCH(lookup_value,lookup_array,[match_type])The Formula
=INDEX(C2:C10,MATCH(F4,B2:B10,0))
Formula Argument Explanation
- Here, F4 is the lookup value.
- B2:B10 is the lookup array.
- 0 refers to an exact match between the lookup value and the lookup array.
- MATCH(F4,B2:B10,0) looks for an exact match between F4 and the lookup array B2:B10.
- C2:C10 is the output array.
Alternative Way #1: Vlookup with CHOOSE & VLOOKUP Functions When Lookup Value Is Not in the First Column
The CHOOSE function allows us to choose data ranges from a table and create a virtual table inside a formula.
We can utilize this advantage of creating a virtual table to vlookup when the lookup value is not in the first column.
CHOOSE Function Syntax
Let me show you how the CHOOSE function works.
Formula
=CHOOSE({1,2},B2:B10,C2:C10)Usage Guide
Just insert the above formula in a blank cell.
This is an array formula.
So press ENTER to insert if you are using Excel for Microsoft 365.
Otherwise, press CTRL + SHIFT + ENTER to insert the formula in a blank cell.
How CHOOSE Function Works?
I’ve inserted two different column ranges, B2:B10 and C2:C10 inside the CHOOSE function.
Before the column ranges, I have mentioned the index number as {1,2}.
This means the CHOOSE function creates a two-column table using the ranges B2:B10 and C2:C10. The index number {1,2} makes the first range, B2:B10; the first column of the newly created table. Then it makes the second range, C2:C10; the second column of the table.
If you alter the index number as {2,1}, it will make B2:B10, the second column, and C2:C10 the first column. Have a look.
Using VLOOKUP & CHOOSE Functions Together
Now we know how the CHOOSE function works.
So we can use the CHOOSE function inside the VLOOKUP function to create a virtual table of two columns.
In the first column, we will keep the lookup column. The second column will be the output column that we want to extract using the VLOOKUP function.
Syntax of the Formula
=VLOOKUP (lookup_value, CHOOSE(index_num, value1, [value2], ...), col_index_num, [range_lookup])Formula
=VLOOKUP(F4,CHOOSE({1,2},B2:B10,C2:C10),2,FALSE)
Explanation of the Formula
In the above formula, the CHOOSE function creates a virtual table having two columns.
The first column is B2:B10 which is the lookup column.
Then the second column, C2:C10 is our desired column from where we want to pull data.
Thus, I’ve used 2 as the column index number which indicates column, C2:C10.
The argument FALSE refers to an exact match as usual.
Finally, F4 is the lookup value that looks for Trevor Rios in the lookup column, B2:B10.
Alternative Way #2: Use XLOOKUP When Lookup Value Is Not in the First Column
The XLOOKUP function is available only in Excel for Microsoft 365 and the later versions.
XLOOKUP Function Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])The Formula
=XLOOKUP(F4,B2:B10,C2:C10)
Formula Argument Explanation
- F4 is the lookup value.
- B2:B10 is the lookup array.
- C2:C10 is the return array.
Alternative Way #3: Modify Table Array When Lookup Value Is Not in the First Column
If you still want to use the VLOOKUP function to lookup vertically, you must modify the table array. The modification should be in such a way that the lookup column becomes the first column of the table array.
The Formula
=VLOOKUP(F4,B2:C10,2,FALSE)In this formula, instead of using A2:C10 as the table array, I have used B2:C10. Here, B2:C10 is the lookup array, which is the first column of the table array now.
In all cases, the table array modification may not be possible. In such cases, you better use the INDEX & MATCH combination.
Alternative Way #4: Move Lookup Column to the First When Lookup Value Is Not in the First Column
If your data table is relatively small, then you can try to move the lookup column at the beginning of your data table.
Though this is impractical for a large data table having a lot of columns.
Anyways, here’s how to move the lookup column to the beginning of the dataset.
Usage Guide
Step_1: Select the column first that you want to move.
Step_2: Then place your cursor at the side border of the column header.
Step_3: Press and hold the SHIFT key on your keyboard.
Step_4: Now drag the column header to the left until it reaches the beginning of the data table.
Step_5: Now use the regular formula of the VLOOKUP function.
The Formula
=VLOOKUP(F4,A2:C10,3,FALSE)
Formula Argument Explanation
- F4 is the lookup value.
- A2:C10 is the table array.
- 3 is the column index number.
- FALSE refers to an exact match between the lookup value, F4, and the table array A2:C10.
Conclusion
I’ve discussed 5 different ways to lookup vertically across columns when the lookup value is not in the first column. I hope you’ve found this article useful. You can read more articles relating to Excel from the Blog page of our website. Have a fantastic workday!
