📎 This article includes 1 downloadable practice file ↓

Almost every Excel job I have done comes down to one question sooner or later: “Can you pull the price (or name, or cost centre) from the other sheet?” Excel gives you three main ways to do it. They all look similar, but they behave very differently when your data changes.

If you want to see the difference before reading, open the Formula Lab — it animates all three formulas searching the same table.

The short answer

If everyone who opens your file has Microsoft 365, use XLOOKUP. If the file goes to colleagues, clients or an SAP/Oracle export process that still uses Excel 2016, use INDEX-MATCH. Keep VLOOKUP for quick one-off checks.

VLOOKUP: simple, but fragile

=VLOOKUP(G2, A2:D100, 4, FALSE)

VLOOKUP searches the first column of the range and returns the value from column number 4. Two things bite people:

• The hard-coded 4. Insert a column inside A:D and your formula quietly returns the wrong column. No error — just wrong numbers in the report.
• Forgetting FALSE. Without it, VLOOKUP does an approximate match on unsorted data and returns a random-looking neighbour. I have seen month-end reports go out like this.

INDEX-MATCH: the reliable workhorse

=INDEX(D2:D100, MATCH(G2, A2:A100, 0))

MATCH finds the row number where G2 appears in column A. INDEX returns the value at that row from column D. Because the lookup column and the return column are separate ranges, you can return from a column on the left, and inserted columns do not break it.

XLOOKUP: the modern default

=XLOOKUP(G2, A2:A100, D2:D100, "Not found")

XLOOKUP combines the best parts of both: separate lookup and return ranges, exact match by default, and a fourth argument for the “not found” case so you no longer need IFERROR. It can also search from the bottom up (search_mode -1), which is handy for “latest price” lookups.

=XLOOKUP(G2, A2:A100, D2:D100, "Not found", 0, -1)   'last match instead of first

What about speed?

On normal business files (a few thousand rows) you will not notice a difference. On very large sheets the bigger win is using exact ranges (A2:A5000) or Excel Tables instead of whole columns, and avoiding thousands of volatile functions like INDIRECT and OFFSET.

My rule of thumb

1. Shared with unknown Excel versions → INDEX-MATCH.
2. Your team is on Microsoft 365 → XLOOKUP.
3. Throwaway check → VLOOKUP with FALSE, and delete it afterwards.

FAQ

Why does my lookup return #N/A when the value is clearly there?

Usually hidden spaces or numbers stored as text (common in SAP exports). Try =TRIM() on the lookup value, or convert with =VALUE().

Can XLOOKUP return several columns at once?

Yes — give it a multi-column return range such as B2:D100 and the result spills across.

Try it yourself: step by step

1. Download lookup-practice.xlsx from the Practice files box below and open it.
2. Go to the Lookups sheet. Cell B2 (yellow) holds the product code P-105. All five methods in column B return 2,799.
3. Change B2 to P-999. VLOOKUP and INDEX-MATCH show #N/A; the XLOOKUP and IFERROR versions show “Not found”. That is why a “not found” value matters in reports.
4. Now go to Products, right-click column C and choose Insert. Back on Lookups: VLOOKUP now returns the category text instead of the price, while INDEX-MATCH and XLOOKUP still return 2,799. Press Ctrl+Z to undo.
5. Row 9 performs a left lookup: type Mouse into B11 and INDEX-MATCH returns its code P-102 — something VLOOKUP cannot do.
6. Open column C on the Lookups sheet to see every formula as text, and copy the one you need into your own workbook.

Common mistakes (and how to spot them)

• Forgetting FALSE / 0 for exact match — the result looks plausible but belongs to a neighbouring row.
• Unlocked ranges: when you copy the formula down, A2:D8 shifts to A3:D9. Press F4 to make it $A$2:$D$8.
• Codes with spaces: “P-105 ” (trailing space) never matches. Wrap the lookup value in TRIM().
• Numbers vs text: 105 and “105” are different values. Check with =ISNUMBER(A2).

Frequently asked questions

Is XLOOKUP slower than VLOOKUP?

Not in any way you will notice on normal business files. Whole-column references (A:A) and thousands of volatile functions slow workbooks down far more than the choice of lookup.

Can I use XLOOKUP if my colleague has Excel 2016?

They will see #NAME?. Use INDEX-MATCH for files that travel.

What is the difference between #N/A and #REF!?

#N/A means the value was not found. #REF! means the formula points to cells that no longer exist, e.g. a column index larger than the range in VLOOKUP.