Table of Contents
By default, the VLOOKUP function in Excel is only able to return values to the right of the lookup value.
To instead return values to the left of the lookup value, you must use the XLOOKUP function.
The following example shows how to use this function in practice.
Example: How to Use VLOOKUP to the Left in Excel
Suppose we have the following dataset in Excel that shows the points scored by various basketball players:
Suppose we use the following formula with VLOOKUP to look up the team “Kings” in column A and return the corresponding points value in column B:
=VLOOKUP("Kings", A2:B11, 2, FALSE)
The following screenshot shows how to use this formula in practice:
Since the value we wanted to return was to the right of the lookup value, the formula was able to correctly return 13.
However, suppose the points column was to the left of the team column and we tried to use VLOOKUP to return the points value:
Since the value we wanted to return was to the left of the lookup value, the VLOOKUP function returned #N/A.
Instead, we must use the XLOOKUP function with the following syntax:
=XLOOKUP("Kings", B2:B11, A2:A11)
The following screenshot shows how to use this formula in practice:
The XLOOKUP function returns the value 13, which is the correct points value that corresponds to the “Kings” in the team column.
Note: You can find the complete documentation for the XLOOKUP function in Excel .