XLOOKUP vs VLOOKUP: Syntax, Differences, and When to Switch
Side-by-side XLOOKUP and VLOOKUP with the same examples: exact match, left lookups, multiple criteria, if-not-found, and the errors each one throws.
10 min read · Updated September 24, 2026
Have your orders and products sheets open?Ask it directly →
VLOOKUP has been Excel's lookup function since 1985, and its quirks — counting columns by hand, only looking rightward, defaulting to an approximate match — have produced more wrong spreadsheets than any other single feature. XLOOKUP (Excel 2021 and Microsoft 365) fixes all of them. If you have it, use it. This guide shows why, with every example done both ways.
The data
A product table in A1:C4 and an orders sheet that needs prices filled in.
| SKU | Product | Price |
|---|---|---|
| W-100 | Widget | 20.00 |
| G-200 | Gadget | 100.00 |
| Z-300 | Gizmo | 90.00 |
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
The shape is the whole difference. VLOOKUP takes a block and a column number. XLOOKUP takes the column to search and the column to return — two ranges, no counting.
Add the price from the Products sheet to each order and total revenue by region.
$186,420 across 2,138 matched orders. North leads at $61,300, then East $52,840, West $41,780 and South $30,500.
12 orders have a SKU that isn't in Products (e.g. X-999) and are left out. In Excel: =XLOOKUP(B2, Products!A:A, Products!C:C, "Not found") * C2 for each order's revenue.
Example 1: exact match
Price for SKU "G-200".
VLOOKUP
=VLOOKUP("G-200", A2:C4, 3, FALSE)
XLOOKUP
=XLOOKUP("G-200", A2:A4, C2:C4)
Both return 100. Notice two things in the VLOOKUP: the 3 (you counted columns), and the FALSE (without it VLOOKUP does an approximate match on unsorted data and returns whatever it lands on — the source of countless silent errors). XLOOKUP defaults to exact match.
Example 2: looking left
Find the SKU for the product named "Gizmo". The answer is to the left of the lookup column.
VLOOKUP — can't. The lookup column must be the first column of the block. The workaround is INDEX/MATCH:
=INDEX(A2:A4, MATCH("Gizmo", B2:B4, 0))
XLOOKUP
=XLOOKUP("Gizmo", B2:B4, A2:A4)
Same shape as Example 1. Direction is irrelevant.
Example 3: not found
Look up a SKU that doesn't exist.
VLOOKUP returns #N/A. To show something friendlier you wrap it:
=IFERROR(VLOOKUP("X-999", A2:C4, 3, FALSE), "Not found")
(IFERROR also hides every other error, including a typo in the range — which is why auditors dislike it.)
XLOOKUP has a built-in fourth argument:
=XLOOKUP("X-999", A2:A4, C2:C4, "Not found")
Only a genuine miss produces "Not found"; other errors still surface.
Example 4: inserting a column breaks one of them
Insert a "Category" column between Product and Price.
VLOOKUP with 3 now returns the category, not the price, and says nothing. Every VLOOKUP pointing at that table is now wrong.
XLOOKUP referenced C2:C4 by range; Excel shifts the reference to D2:D4 automatically. Still correct.
Example 5: multiple criteria
Price for product "Widget" in region "North", from a table with Region, Product, Price.
VLOOKUP needs a helper column that concatenates Region & Product, then looks that up.
XLOOKUP can build the key inline:
=XLOOKUP("North"&"Widget", A2:A50&B2:B50, C2:C50)
The & between ranges creates an array of combined keys on the fly.
Example 6: return several columns at once
VLOOKUP: one formula per column.
XLOOKUP: return a multi-column range and it spills:
=XLOOKUP("G-200", A2:A4, B2:C4)
→ Gadget | 100.00, in two adjacent cells.
Example 7: last match, and approximate match done properly
Find the most recent order for a customer (data sorted oldest → newest):
=XLOOKUP(customer, A:A, D:D, , 0, -1)
The -1 search mode searches from the bottom. VLOOKUP has no equivalent.
Tax bracket lookup (find the largest threshold ≤ income):
=XLOOKUP(income, Thresholds, Rates, , -1)
Match mode -1 = exact or next smaller. This is what VLOOKUP's TRUE was for, but XLOOKUP doesn't require the thresholds to be sorted.
Errors and what they mean
| Error | VLOOKUP | XLOOKUP |
|---|---|---|
#N/A | Not found — or approximate match on unsorted data | Not found (and you can replace it with the 4th argument) |
#REF! | Column number exceeds the block | Return array is a different size from the lookup array |
#VALUE! | Column number is 0 or negative | Rarely; usually a text/number mismatch in the key |
| Wrong value, no error | Column inserted/deleted; range_lookup omitted | Almost never |
Should you still learn VLOOKUP?
Yes, to read it — it's in millions of existing workbooks — and to write it when a file must open in Excel 2019 or earlier, or in tools that don't support dynamic arrays. For anything new in Microsoft 365, XLOOKUP is shorter, safer, and can't be broken by inserting a column.
The shortcut
If the lookup is a one-off — "what's the price of G-200?", "which customers ordered Gizmos?" — you don't need a formula. Upload both sheets to ChatExcel and ask. Multi-sheet workbooks are loaded with each sheet as its own table, so "add the price from the Products sheet to each order and total by region" works without a lookup at all. When you need the formula to live in the file, ask "how do I do that in Excel?" and you'll get the XLOOKUP above with your real column letters.
- Is XLOOKUP available in my version of Excel?
- XLOOKUP is in Excel for Microsoft 365, Excel 2021 and later, Excel for the web, and Google Sheets. Excel 2019 and earlier do not have it; use INDEX/MATCH there.
- Is XLOOKUP faster than VLOOKUP?
- On large sheets they're comparable for exact matches. XLOOKUP is faster to write and far less error-prone; if performance matters, use binary search mode (search_mode 2) on sorted data.
- Can XLOOKUP replace INDEX/MATCH?
- In nearly every case, yes — left lookups, multiple criteria, and multi-column returns are all built in. INDEX/MATCH remains useful for two-dimensional lookups (row and column), though XLOOKUP nested in XLOOKUP handles that too.
- Why does VLOOKUP return the wrong value with no error?
- Most often the fourth argument was omitted, so VLOOKUP did an approximate match on unsorted data. Always pass FALSE for exact match — or switch to XLOOKUP, which defaults to exact.