How to Perform a Reverse Lookup in Excel?
Reverse Lookup with XLOOKUP is a Excel function that looks up a value in a specified column and returns a corresponding value from a column to the left.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Need to find values to the left of a lookup column? Use XLOOKUP for efficient reverse lookups in your Excel sheets.
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 any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.