Soon after learning VLOOKUP we realize it is hard to return a value left of the lookup range. Till now we resort to workarounds or use INDEX & MATCH functions together. These techniques require at least two functions together in a formula to do excel left lookup.
Excel XLOOKUP makes you do left lookup and even more with one single function in the Excel formula.
In this tutorial:
With XLOOKUP it is super easy to look up to the left of the data range as it not only let us independently select the lookup range but also the return range.
Why VLOOKUP can’t look left?
With VLOOKUP it’s quite easy to return a value from the specific column to the right of the lookup column.
In the above, if I have to find what store Ibrahim is dealing, I can instantly know with the following formula:
=VLOOKUP("Ibrahim",A2:C8,3,FALSE)
If I need the sales amount for the same agent, I simply need to change the column number from 3 to 2, and Excel VLOOKUP will get the value for me.
=VLOOKUP("Ibrahim",A2:C8,2,FALSE)
So we can establish with VLOOKUP function by default:
- the first column or lookup column or column #1 or the left-most column, is meant for lookup value only
- and all the remaining columns to the right i.e. column #2 and onwards, are simply meant to get the relevant return value.
However, with the same data available, what if we like to get the agent name based on ID#?
With VLOOKUP alone, we simply CANNOT.
Because the lookup column is the right-most column in the data range, and the return column is on the left of the lookup column.
In short, to perform VLOOKUP we always move from left to right and not the other way around.
To solve this problem we:
- either rearrange the data i.e. move the ID column to the left of the Agent column, or
- use VLOOKUP with CHOOSE function to lookup to the left, or
- use INDEX & MATCH function to Excel left lookup, or
- simply use XLOOKUP!
Excel lookup to the left with XLOOKUP
Excel XLOOKUP function gives full control over the selection of lookup array and return array.
The following compares the syntax of both XLOOKUP and VLOOKUP:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
lookup_array Vs table_array
Unlike Excel VLOOKUP function where only on the first column in the selection is the lookup array, in XLOOKUP you can manually select any column within the lookup range.
The major difference between two functions regarding lookup range is table_array under VLOOKUP includes both lookup column and return columns. Whereas lookup_array under XLOOKUP is only meant for lookup purposes.
return_array Vs col_index_num
Similarly, XLOOKUP gives us full control over the return column as opposed to column numbers in VLOOKUP thus making the lookup more flexible.
As discussed earlier. under VLOOKUP col_index_num can only be greater than 1 i.e. 2 and onwards, and not even -1 or -2, we are restricted to only move towards the right of the lookup column.
With the syntax understood in detail, here is our problem once again:
We like to find the name for ID # 7767. Here is the XLOOKUP function once again:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
In our case;
- lookup_value = 7767
- lookup_array = D2:D8; as column D contains the ID numbers from row 2 to row 8
- return_array = A2:A8; as column A contains the ID numbers from row 2 to 8
And the formula looks like this:
=XLOOKUP(7767,D2:D8,A2:A8)
And the name is Esa.
This concludes this tutorial on how to perform excel left lookup or lookup to the left in Excel using XLOOKUP function.
Hope you find it useful!