How to Use XLOOKUP in Excel (Better Than VLOOKUP)
To use XLOOKUP in Excel, write =XLOOKUP(lookup_value, lookup_array, return_array) — it searches one column for your value and returns the matching item from another, in either direction. Unlike VLOOKUP, it can look left, defaults to an exact match, and won’t break when you insert a column.
The syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])| Argument | What it is | Required? |
|---|---|---|
lookup_value | What you’re searching for | Yes |
lookup_array | The column to search | Yes |
return_array | The column to return from | Yes |
if_not_found | What to show when there’s no match | Optional |
match_mode | Exact (0), exact-or-next (1), wildcard (2) | Optional — defaults to exact |
search_mode | First-to-last (1), last-to-first (-1), binary | Optional |
The first three arguments are all you need for a normal lookup.
A worked example
Say you have a product table: A = product ID, B = product name, C = price.
To find the price for the ID in E2:
=XLOOKUP(E2, A2:A100, C2:C100)- Search
A2:A100for the value in E2 - Return the matching value from
C2:C100
That’s it — no column counting, no fourth argument to remember.
What XLOOKUP fixes about VLOOKUP
| Problem with VLOOKUP | XLOOKUP |
|---|---|
| Can only look right of the lookup column | Works either direction |
Must count the column number (3) | You pass the return column directly |
| Defaults to approximate match | Defaults to exact |
| Breaks when you insert a column | Immune — ranges are explicit |
Returns #N/A on no match | Can return your own message |
That last one is a genuine improvement. Instead of an ugly error:
=XLOOKUP(E2, A2:A100, C2:C100, "Not found")Handling no-match properly
The if_not_found argument is worth using in almost every sheet:
=XLOOKUP(E2, A2:A100, C2:C100, "")Empty string for a clean blank, or a message like "Check the ID" to flag a data problem. Either beats a column of #N/A.
Looking left (the VLOOKUP workaround you no longer need)
VLOOKUP can’t return a value from a column to the left of what it searches. XLOOKUP has no such limit:
=XLOOKUP(E2, C2:C100, A2:A100)That searches the price column and returns the product ID — impossible with VLOOKUP without restructuring the data or using INDEX/MATCH.
Returning several columns at once
Point return_array at more than one column and XLOOKUP spills all of them:
=XLOOKUP(E2, A2:A100, B2:D100)That returns the name, price, and stock for the matching row across three cells. It’s a clean way to pull a whole record.
The other optional arguments
Match mode — change how strictly it matches:
| Value | Behaviour |
|---|---|
0 (default) | Exact match only |
-1 | Exact, or the next smaller item |
1 | Exact, or the next larger item |
2 | Wildcard (* and ?) |
Search mode — which end to start from:
| Value | Behaviour |
|---|---|
1 (default) | First to last |
-1 | Last to first — finds the most recent match |
search_mode: -1 is genuinely useful for logs and transaction lists, where you want the latest entry rather than the first.
Availability
XLOOKUP requires Microsoft 365 or Excel 2021 or later. It does not exist in Excel 2019, 2016, or older, and it’s not in Excel for the web’s older compatibility modes.
On an older version, use INDEX/MATCH instead — it does everything XLOOKUP does, just with more typing:
=INDEX(C2:C100, MATCH(E2, A2:A100, 0))If you’re migrating from VLOOKUP, see how to use VLOOKUP in Excel for the differences in practice, and VLOOKUP not working for the errors you can stop worrying about.
Common problems
| Problem | Fix |
|---|---|
#NAME? error | Your Excel version predates XLOOKUP — use INDEX/MATCH |
#VALUE! | The lookup and return arrays are different lengths |
| Returns the wrong row | Duplicate values in the lookup column — XLOOKUP returns the first unless you set search_mode to -1 |
#N/A despite an obvious match | A trailing space or a number stored as text. Try =XLOOKUP(TRIM(E2), ...) |
| Spill error when returning several columns | There isn’t room beside the formula — clear the cells it needs |
For more on the functions around it, Google Sheets formulas for beginners covers the basics, and how to use VLOOKUP in Google Sheets covers the Sheets equivalent if you’re not on Excel.
FAQ
What does XLOOKUP do in Excel?
It searches one range for a value and returns the matching item from another range — in either direction, with an exact match by default.
Is XLOOKUP better than VLOOKUP?
Yes, for almost everything. It looks both left and right, defaults to exact matching, survives column inserts, and lets you set your own “not found” message.
Does XLOOKUP work in older Excel versions?
No — it needs Microsoft 365 or Excel 2021+. On Excel 2019 and earlier, use INDEX/MATCH instead.
Can XLOOKUP return a value to the left?
Yes. Point lookup_array at the right-hand column and return_array at the left one — =XLOOKUP(E2, C2:C100, A2:A100). VLOOKUP can’t do this.
How do I stop XLOOKUP showing #N/A?
Use the fourth argument: =XLOOKUP(E2, A2:A100, C2:C100, "Not found"). It shows your text instead of the error when there’s no match.
Related guides
Google Sheets Formulas for Beginners (10 You Need to Know)
The 10 essential Google Sheets formulas every beginner needs — SUM, AVERAGE, COUNT, IF, and more, with working examples.
How to Add Checkboxes in Google Sheets (Tick Boxes)
Add checkboxes in Google Sheets with Insert → Checkbox, set custom checked/unchecked values, and count ticks with COUNTIF or a progress bar.
How to Create a Drop-Down List in Excel (Data Validation)
Add a drop-down list in Excel with Data Validation — pick from a typed list or a range on another sheet, and fix the errors that stop it working.