XLOOKUP: a simple worked example
XLOOKUP looks for a value in one column, then brings back the value from the same row of another column. It does the job of VLOOKUP without counting columns, and the column it brings back can be on either side.
A worked example
Here's a staff list. Someone asks for Lucy's staff ID.
| A | B | C | |
|---|---|---|---|
| 1 | Staff ID | Employee | Team |
| 2 | 1001 | Grace | Sales |
| 3 | 1002 | Tom | Finance |
| 4 | 1003 | Priya | Support |
| 5 | 1004 | Lucy | Support |
| 6 | 1005 | Marek | Sales |
By hand you would:
- Go down the Employee column until you find Lucy. She's the 4th name.
- Take the 4th value from the Staff ID column: 1004.
The ID is to the left of the name, which VLOOKUP can't do. XLOOKUP doesn't mind, because you give it the column to search and the column to bring back separately.
The formula
=XLOOKUP(F1,B2:B6,A2:A6)
F1is what to look for: the name typed in F1.B2:B6is the column to search.A2:A6is the column to bring back. It must cover the same rows as the column you search.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Staff ID | Employee | Team | Name | Lucy | |
| 2 | 1001 | Grace | Sales | Staff ID | 1004 | |
| 3 | 1002 | Tom | Finance | |||
| 4 | 1003 | Priya | Support | |||
| 5 | 1004 | Lucy | Support | |||
| 6 | 1005 | Marek | Sales |
XLOOKUP matches exactly unless you ask it not to, so there's no FALSE to remember.
Showing a message when there's no match
If the name isn't on the list, XLOOKUP shows #N/A. Add a fourth part to show something friendlier:
=XLOOKUP(F1,B2:B6,A2:A6,"Not found")
XLOOKUP or VLOOKUP?
- XLOOKUP can bring back a column to the left. VLOOKUP can't.
- XLOOKUP doesn't use a column number, so inserting a column doesn't break it.
- XLOOKUP matches exactly by default. VLOOKUP needs FALSE at the end.
- VLOOKUP works in every version of Excel. XLOOKUP needs Excel for Microsoft 365, Excel 2021 or later, or Google Sheets. If you share files with people on older versions, use INDEX and MATCH instead.
Common mistakes
#VALUE!: the two columns are different sizes
B2:B6 and A2:A7 don't line up, so XLOOKUP refuses. Make both ranges start and end on the same rows.
#NAME?: your Excel doesn't have XLOOKUP
Excel 2019 and earlier don't recognise the name. Use INDEX and MATCH, which do the same job.
#N/A when the value looks right
Check for a space at the end of the name, or a number stored as text. TRIM removes stray spaces.
Ranges that move when you copy down
If you copy the formula down a column, lock both ranges with $: =XLOOKUP(E2,$B$2:$B$6,$A$2:$A$6). Press F4 to add the dollar signs.
Try it now
In F2, find the staff ID of the employee named in F1. The IDs are to the left of the names.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Staff ID | Employee | Team | Name | Lucy | |
| 2 | 1001 | Grace | Sales | Staff ID | ||
| 3 | 1002 | Tom | Finance | |||
| 4 | 1003 | Priya | Support | |||
| 5 | 1004 | Marek | Marketing | |||
| 6 | 1005 | Nia | Sales | |||
| 7 | 1006 | Ollie | Finance | |||
| 8 | 1007 | Lucy | Support | |||
| 9 | 1008 | Hamza | Marketing |
Where it works
Excel for Microsoft 365, Excel 2021 onwards, Excel for the web, Google Sheets and LibreOffice Calc 24.8 onwards.