XLOOKUP function
XLOOKUP searches one column or row for a value and returns the matching item from another. It replaces VLOOKUP, HLOOKUP and most INDEX/MATCH combinations: it can look left, it uses an exact match unless you say otherwise, and it has a built-in fallback for values it can't find.
- Available in
- Excel 2021, 2024 and Microsoft 365
- Category
- Lookup functions
- Returns
- The matching value. If return_array is several columns wide, the whole matching row spills to the right.
- Google Sheets
- Available in Google Sheets with the same arguments.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | Required? | What it is |
|---|---|---|
lookup_value | Required | The value to find, usually a cell reference such as an ID. |
lookup_array | Required | The single column or row to search. |
return_array | Required | The column or row to return from. It must be the same height (or width) as lookup_array. |
if_not_found | Optional | What to return when there is no match, instead of #N/A. |
match_mode | Optional | 0 exact match (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match. |
search_mode | Optional | 1 searches first to last (default), -1 last to first. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | =XLOOKUP(E2,A2:A5,C2:C5) | $54 | Price for the SKU typed in E2 |
| F3 | =XLOOKUP("Monitor arm",B2:B5,A2:A5) | B-210 | Looks left: the SKU for a product name |
| F4 | =XLOOKUP("Z-999",A2:A5,C2:C5,"Not found") | Not found | A missing SKU returns the fallback text instead of #N/A |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| SKU | Product | Price | SKU to find | Result | |
| A-100 | Desk lamp | $39 | C-330 | $54 | |
| B-210 | Monitor arm | $89 | B-210 | ||
| C-330 | Laptop stand | $54 | Not found | ||
| D-440 | USB hub | $29 |
Practice XLOOKUP
From lesson 10, XLOOKUP. Graded instantly, then re-checked on changed data.
ID to name
G2, return the name for the employee ID in F2.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #N/A | The value isn't in lookup_array. Look for extra spaces or numbers stored as text, or add an if_not_found argument so the formula says so plainly. |
| #VALUE! | lookup_array and return_array are different sizes. Make both ranges start and end on the same rows. |
| #NAME? | Your version doesn't have XLOOKUP (Excel 2019 or earlier). Use INDEX and MATCH, which work everywhere. |
| Wrong results after filling down | The ranges shifted as the formula moved. Lock them with $, for example $A$2:$A$5. |
Tips
- Return several columns at once by making return_array wider, such as B2:C5. The results spill to the right.
- Use search_mode -1 to get the most recent entry from a log that is sorted oldest first.
Questions
Is XLOOKUP better than VLOOKUP?
For most work, yes. It doesn't need a column number, it can return columns to the left of the search column, and it defaults to an exact match, which avoids VLOOKUP's most common silent error.
Does XLOOKUP work in Excel 2019?
No. It's available in Excel 2021, Excel 2024 and Microsoft 365. Files that use it show #NAME? in older versions.
Can XLOOKUP match on two criteria?
Yes. Join the criteria with &, for example =XLOOKUP(G2&H2, A2:A9&B2:B9, C2:C9).
Lessons that use XLOOKUP
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.