How to Perform a Reverse Lookup in Excel?
Need to find values to the left of a lookup column? Use XLOOKUP for efficient reverse lookups in your Excel sheets.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Look up a value in a column and return a value from a column that is to the LEFT of the lookup column"
=XLOOKUP(lookup_value, lookup_array, return_array, , , -1)This formula looks up a value in a specified column and returns a corresponding value from a column to the left.
Step-by-step breakdown
- lookup_value: The value you want to find in the lookup_array.
- lookup_array: The column where the lookup_value is searched.
- return_array: The column from which the corresponding value is returned.
- The last argument (-1) specifies that the search should be done from the last to the first.
Edge cases & warnings
- The lookup_value is not found in the lookup_array.
- The return_array is shorter than the lookup_array.
- The lookup_array contains duplicate values.
- The lookup_value is blank or an error.
Examples
"Lookup 'Banana' in column B and return value from column A"
'Fruit'"Lookup 'Item3' in column D and return value from column C"
'Value3'Frequently asked questions
Can XLOOKUP return multiple values?
No, XLOOKUP returns only a single corresponding value.
What happens if the lookup value is not found?
XLOOKUP will return an error unless a default value is specified.
Is XLOOKUP available in all versions of Excel?
XLOOKUP is available in Excel 365 and Excel 2021.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.