Table of Contents
VLOOKUP is a function in Microsoft Excel that allows users to search for a specific value within a table and return a corresponding value from a different column. By default, VLOOKUP searches for the value in the first column of the table and returns a value from a column to the right. However, it is possible to use VLOOKUP to the left by rearranging the columns in the table. This can be useful when the desired lookup value is located in a column to the left of the column containing the desired return value. By rearranging the columns, the desired return value can be placed in the first column, allowing VLOOKUP to search for the lookup value to the left and return the corresponding value. This can be done by simply dragging and dropping the columns in the table, or by using the INDEX and MATCH functions in conjunction with VLOOKUP. By utilizing VLOOKUP to the left, users can efficiently retrieve data from a table in Excel.
Excel: Use VLOOKUP to the Left
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 .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: