XLOOKUP vs VLOOKUP vs INDEX/MATCH: the real differences, on a real workbook
The three lookups compared side by side, then used on a 500-order workbook. A lookup to the left, the inserted-column trap that silently breaks VLOOKUP, and which one to use.
VLOOKUP, INDEX/MATCH and XLOOKUP all do the same basic job: find a value in one column and return a
value from the same row. Most comparisons stop at a feature table. This one also uses all three on a real
workbook, so you can see where they agree and where one of them quietly gives you the wrong answer.
The differences, side by side
| VLOOKUP | INDEX/MATCH | XLOOKUP | |
|---|---|---|---|
| Look up to the left | No | Yes | Yes |
| Survives an inserted column | No (column number) | Yes | Yes |
| Exact match by default | No (needs FALSE) | No (needs 0) | Yes |
| Value if not found | Needs IFERROR | Needs IFERROR | Built in |
| Works in | Every version | Every version | Excel 2021, Microsoft 365, Google Sheets |
The workbook
A price list and an order log:
- Orders: Order ID in column A, Product ID in B, Units in C, 500 orders in rows 2 to 501.
- Products: Product in A, Category in B, Product ID in C, Unit Price in D, 25 products in rows 2 to 26.
The task: put each order's unit price in column D of Orders.
The same price, three ways
In D2 of Orders, any of these works:
=VLOOKUP(B2, Products!$C$2:$D$26, 2, FALSE)=INDEX(Products!$D$2:$D$26, MATCH(B2, Products!$C$2:$C$26, 0))=XLOOKUP(B2, Products!$C$2:$C$26, Products!$D$2:$D$26, "Not found")Filled down, all three return the same price for 497 of the 500 orders (the first, ORD-3001, is $45).
The other three, in rows 304, 323 and 359, use product P-150, which isn't on the Products sheet.
VLOOKUP and INDEX/MATCH show #N/A there; XLOOKUP shows Not found, because of its fourth argument.
That #N/A is useful, not a nuisance: it's how you find orders for discontinued products. Wrapping a lookup in
IFERROR(..., 0) hides it and turns a missing price into a free order.
Looking up to the left
Now put the product name in column E. The name is in column A of Products, to the left of the
Product ID in column C. VLOOKUP can only return columns to the right of the one it searches, so it can't do
this without moving columns around. The other two can:
=XLOOKUP(B2, Products!$C$2:$C$26, Products!$A$2:$A$26)=INDEX(Products!$A$2:$A$26, MATCH(B2, Products!$C$2:$C$26, 0))For ORD-3001 (product P-111) both return Milk Frother.
The inserted-column trap
Someone adds a Supplier column to Products, right after Product ID. Unit Price moves from column D to E.
- VLOOKUP breaks, silently. Excel stretches its range to
Products!$C$2:$E$26, but the column number in the formula stays 2, and column 2 of that range is now Supplier. All 497 prices turn into the supplier's name. There's no error to warn you; the sheet just has text where the prices were. - INDEX/MATCH and XLOOKUP keep working. Their ranges point at the price column itself, so Excel moves them to column E and every price stays right.
This is the strongest reason to stop using VLOOKUP in workbooks other people edit.
Which one to use
- XLOOKUP, if everyone who opens the file has Excel 2021, Microsoft 365 or Google Sheets. It's exact by default, looks left, survives inserted columns, and handles missing values itself.
- INDEX/MATCH, if the file goes to people on older Excel. It does everything
XLOOKUPdoes here, with two functions instead of one. - VLOOKUP only for a quick, one-off lookup to the right, and always with
FALSEat the end.
For this workbook, that's =XLOOKUP(B2, Products!$C$2:$C$26, Products!$D$2:$D$26, "Not found") in D2, filled
down to row 501.
Let GPTExcel write it for your sheet
Attach your workbook to GPTExcel and ask "Write the unit price lookup for column D of Orders". It reads your real sheet and column names, so the ranges are right the first time. Ask for all three versions, or tell it which Excel version your team uses. More in the Formulas guide, and the other modern functions worth knowing are in Five modern Excel formulas every analyst should know.