How to / Lookups
How to look up a value to the left in Excel
VLOOKUP cannot look left. Use =XLOOKUP("P-2", B2:B4, A2:A4): it takes the column to search and the column to return separately.
The name for a code, when the names are on the left
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Code | ||
| 2 | Ana | P-1 | Ben | |
| 3 | Ben | P-2 | ||
| 4 | Chloe | P-3 |
In D2: =XLOOKUP("P-2", B2:B4, A2:A4) → Ben
How it works
- The first range,
B2:B4, is where XLOOKUP searches for the code. - The second range,
A2:A4, is where the answer comes from. - The two can be in any order, on either side of each other.
Variations
In older versions of Excel
A B C D 1 Name Code 2 Ana P-1 Ben 3 Ben P-2 4 Chloe P-3 In D2:
=INDEX(A2:A4, MATCH("P-2", B2:B4, 0))→ BenMATCH finds the row of the code; INDEX returns the name on that row.
Your own text when the code is missing
A B C D 1 Name Code 2 Ana P-1 Not found 3 Ben P-2 4 Chloe P-3 In D2:
=XLOOKUP("P-9", B2:B4, A2:A4, "Not found")→ Not foundWhat VLOOKUP does with the same table
A B C D 1 Name Code 2 Ana P-1 #N/A 3 Ben P-2 4 Chloe P-3 In D2:
=VLOOKUP("P-2", A2:B4, 1, FALSE)→ #N/AIt searches column A, the first column of the table, where there are no codes.
Watch out for
- Ranges of different sizes. The search range and the return range must have the same number of rows, or XLOOKUP returns #VALUE!.
- XLOOKUP shows #NAME? XLOOKUP needs Excel 2021 or Microsoft 365. In older versions, use the INDEX and MATCH formula.
Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.
Functions used
- XLOOKUP: Searches a range and returns the matching item from another range. The modern replacement for VLOOKUP.
- INDEX: The value at a given row and column of a range.
- MATCH: The position of a value in a row or column.
- VLOOKUP: Finds a value in the first column of a table and returns a value from the same row.