Excel XLOOKUP
XLOOKUP searches one column (or row) for a value and returns the value from the same position in another column. For an exact match, write =XLOOKUP(what, where_to_look, what_to_return). It can look left, and it does an exact match unless you ask for something else.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
lookup_value- The value to find, usually a cell such as
F2. lookup_array- The single column (or row) to search, for example
A2:A9. return_array- The column (or row) to take the answer from. It must have the same number of rows as
lookup_array, and it can be to the left or right of it. if_not_found- Optional. What to show when the value is not found, instead of
#N/A. Text goes in quotes, for example"Not found". match_mode- Optional.
0is an exact match (the default).-1is exact, or else the next smaller value.1is exact, or else the next larger value.2allows the wildcards*and?. search_mode- Optional.
1searches from the first item to the last (the default).-1searches from the last item to the first, so you get the last match.
How XLOOKUP works
XLOOKUP takes two separate columns: one to search in and one to answer from. It finds the position of your value in the first column and returns whatever sits at that position in the second. Because the two columns are independent, it does not matter which one is on the left.
In the sheet below, employee IDs are in A2:A9 and names are in B2:B9. To get the name that belongs to ID E105:
=XLOOKUP("E105", A2:A9, B2:B9)
- XLOOKUP searches
A2:A9for"E105"and finds it in the 5th row. - It takes the 5th item of
B2:B9. - It returns
Elena.
As with any lookup, you normally point at a cell instead of typing the value: =XLOOKUP(F2, A2:A9, B2:B9).
Why people prefer it to VLOOKUP
- It looks in any direction. The return column can be left of the lookup column.
- It does an exact match by default, so there is no
FALSEto forget. - It points at a column, not a column number, so inserting or deleting columns does not break it.
- It has a built-in
if_not_foundargument, so you do not needIFERROR. - It can search from the bottom up, which is how you find the last match.
Bands with match_mode
For bands such as bonus tiers, set match_mode to -1. XLOOKUP then returns the row with the largest value that is less than or equal to the one you asked for. Unlike an approximate VLOOKUP, the table does not have to be sorted.
When not to use it
XLOOKUP exists in Excel 2021 and later, Excel for Microsoft 365 and Excel for the web. In Excel 2019 and older, and in files you share with people who use them, use INDEX MATCH or VLOOKUP instead.
Practise XLOOKUP
Click a numbered cell, type a formula and press Enter. Each answer is checked straight away, then tested again on changed data, so the result has to come from the formula. While typing a formula, click or drag across cells to insert their references. Yellow cells are inputs you can change.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ID | Name | Department | Salary | Lookup | Result | Units from | Bonus | |||
| 2 | E101 | Anna | Sales | 3200 | E105 | 0 | 0% | ||||
| 3 | E102 | Boris | Support | 2800 | Greta | 100 | 2% | ||||
| 4 | E103 | Clara | Sales | 3500 | Ivan | 250 | 5% | ||||
| 5 | E104 | David | Finance | 4100 | Support | 500 | 8% | ||||
| 6 | E105 | Elena | Support | 2950 | |||||||
| 7 | E106 | Filip | Sales | 3300 | Units sold | Bonus | |||||
| 8 | E107 | Greta | Finance | 3900 | 320 | ||||||
| 9 | E108 | Hugo | Support | 2700 | |||||||
| 10 | |||||||||||
| 11 | |||||||||||
| 12 |
-
1In
G2, find the name of the employee whose ID is inF2.Show answer
=XLOOKUP(F2,A2:A9,B2:B9) -
2In
G3, find the ID of the employee whose name is inF3. The ID column is to the left of the name column, which VLOOKUP cannot do.Show answer
=XLOOKUP(F3,B2:B9,A2:A9) -
3
F4holds a name that is not in the list. InG4, look up the department of the employee named inF4, but showNot foundwhen there is no such employee. Use XLOOKUP only, without IFERROR.Show answer
=XLOOKUP(F4,B2:B9,C2:C9,"Not found") -
4Several employees can share a department. In
G5, find the salary of the last employee in the list who works in the department named inF5. A plain XLOOKUP returns the first match, so change the search direction.Show answer
=XLOOKUP(F5,C2:C9,D2:D9,,0,-1) -
5The table in
I1:J5lists bonus bands: 100 or more units earn 2%, 250 or more earn 5%, and so on. InG8, find the bonus for the units sold inF8using thematch_modeargument.Show answer
=XLOOKUP(F8,I2:I5,J2:J5,,-1)
Common XLOOKUP problems
#N/A
The value was not found. Check for typing differences and extra spaces (TRIM removes them), and for numbers stored as text on one side. If a miss is normal, set the if_not_found argument. If you expected a band match, set match_mode to -1 or 1.
#VALUE!
lookup_array and return_array do not have the same size, or lookup_array has more than one column. Search one column and return a column of the same height.
#SPILL!
If return_array covers several columns, XLOOKUP returns the whole row and spreads it over neighbouring cells. Those cells must be empty. Return a single column if you want a single value.
#NAME?
Your version of Excel does not have XLOOKUP, which needs Excel 2021 or later, or Microsoft 365. Use INDEX MATCH instead.
Right-looking result, wrong row
Two columns of different heights shift the answer. Make lookup_array and return_array start and end in the same rows, for example A2:A9 and B2:B9.
Questions
Is XLOOKUP available in Excel 2016 or 2019?
No. XLOOKUP is in Excel 2021 and later, Excel for Microsoft 365 and Excel for the web. In older versions, use INDEX with MATCH, or VLOOKUP.
What is the difference between XLOOKUP and VLOOKUP?
XLOOKUP searches one column and returns another, so it can look left. It does an exact match by default, uses column references instead of a column number, has an if_not_found argument, and can search from the bottom. VLOOKUP only returns columns to the right of the one it searches.
Can XLOOKUP return more than one column?
Yes. If return_array covers several columns, such as B2:D9, XLOOKUP returns the whole row and spills it into the cells next to the formula. Those cells must be empty, or you get #SPILL!.
Is XLOOKUP case-sensitive?
No. "anna" and "Anna" match each other. To compare case exactly you need a formula built on EXACT.
Does XLOOKUP support wildcards?
Only if you ask for them. Set match_mode to 2, then * matches any number of characters and ? matches one. With the default exact match, an asterisk is treated as an ordinary character.
How do I find the last match with XLOOKUP?
Set search_mode to -1, the sixth argument. XLOOKUP then searches from the bottom of the list upwards and returns the last match.