Excel functions / Lookup & reference
XLOOKUP
Searches one range and returns the matching item from another. A modern, simpler replacement for VLOOKUP and HLOOKUP.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])Arguments
lookup_value | What you are searching for. |
|---|---|
lookup_array | The column or row to search. |
return_array | The column or row to return a value from. |
if_not_found | Optional. What to show when there is no match, instead of #N/A. |
How it works
XLOOKUP asks two simple questions: where should I look, and what should I bring back? You point at the two ranges directly, so there is no column number to count.
It matches exactly by default, it can return values to the left of the search column, and it has its own "not found" message built in. It is available in Excel 2021 and Microsoft 365.
Example
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | Stock | ||
| 2 | A10 | Lamp | $25.00 | 12 | $85.00 | |
| 3 | B20 | Desk | $140.00 | 3 | ||
| 4 | C30 | Chair | $85.00 | 7 | ||
| 5 | D40 | Shelf | $60.00 | 0 | ||
| 6 |
Result: $85.00. Looks for Chair in the product names and returns the price from the same row.
Tips
- The two ranges must be the same size, such as B2:B5 and C2:C5.
- If you share files with people on older Excel, use INDEX and MATCH instead.
Practise XLOOKUP
Try it yourself in a real spreadsheet, with instant feedback on every answer.