VLOOKUP vs XLOOKUP vs INDEX MATCH: Which to Use
· 8 min read
Short answer on VLOOKUP vs XLOOKUP: use XLOOKUP if everyone who opens the file has Excel 2021, Microsoft 365 or Excel for the web. It does exact match by default, can look left, and does not break when someone inserts a column. Use INDEX MATCH when the file must also work in Excel 2019 or older. Use VLOOKUP only for quick, throwaway lookups, and always with FALSE at the end. All three return the same price here:
=XLOOKUP(E2, A2:A6, C2:C6)
=INDEX(C2:C6, MATCH(E2, A2:A6, 0))
=VLOOKUP(E2, A2:C6, 3, FALSE)The example table
A supplies company keeps a price list. You want the price of the product code typed in E2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | Look up | Method | Result | |
| 2 | P-101 | Stapler | 12.50 | P-104 | XLOOKUP | 3.90 | |
| 3 | P-102 | Paper ream | 6.80 | Coffee filters | INDEX MATCH | 3.90 | |
| 4 | P-103 | Coffee filters | 4.25 | P-199 | VLOOKUP | 3.90 | |
| 5 | P-104 | Dish soap | 3.90 | MATCH on its own | 4 | ||
| 6 | P-105 | Floor cleaner | 9.60 | Code for E3, XLOOKUP | P-103 | ||
| 7 | Code for E3, INDEX MATCH | P-103 | |||||
| 8 | E4 with a fallback | Not in list |
E2 contains P-104. All three formulas above return 3.90.
How each one works
VLOOKUP
=VLOOKUP(lookup_value, table, column_number, FALSE)VLOOKUP searches the first column of the table and returns the value from the column number you give it. 3 means "third column of A2:C6", which is C. FALSE means exact match.
Two limits come from that design. It can only look to the right of the first column, and the 3 is typed by hand. If someone inserts a column between A and C, the formula still says 3 and quietly returns the wrong column.
XLOOKUP
=XLOOKUP(lookup_value, lookup_range, return_range, [if_not_found], [match_mode])You point at the column to search and the column to return. No counting. Exact match is the default, and the optional fourth argument replaces the usual IFERROR wrapper:
=XLOOKUP(E4, A2:A6, C2:C6, "Not in list")In the sheet above, E4 holds P-199, a code that is not on the list, so G8 shows Not in list instead of #N/A.
XLOOKUP is available in Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. In Excel 2019 and earlier it shows #NAME?.
INDEX MATCH
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))MATCH finds the position of P-104 in A2:A6. It is the 4th cell, so MATCH returns 4 (G5 in the sheet shows MATCH on its own). INDEX then returns the 4th cell of C2:C6, which is 3.90. The 0 means exact match. It works in every version of Excel.
Comparison table
| VLOOKUP | XLOOKUP | INDEX MATCH | |
|---|---|---|---|
| Default match | Approximate (if you omit the 4th argument) | Exact | Depends on MATCH's 3rd argument |
| Look to the left | No | Yes | Yes |
| Survives inserted columns | No | Yes | Yes |
| Built-in "not found" value | No | Yes | No |
| Works in Excel 2019 and older | Yes | No | Yes |
Looking to the left: vlookup vs index match
Now flip the question. You know the product name, "Coffee filters", in E3, and want its code. The code is in column A, to the left of the names. VLOOKUP cannot do this without rearranging the table. The other two can:
=XLOOKUP(E3, B2:B6, A2:A6)
=INDEX(A2:A6, MATCH(E3, B2:B6, 0))Both return P-103 (G6 and G7 in the sheet above). This is the most common reason people move from VLOOKUP to INDEX MATCH.
XLOOKUP vs INDEX MATCH
If both work, which is better? For a beginner, XLOOKUP. It is one function, the arguments read in a natural order, and you cannot forget the exact-match flag because exact is the default.
INDEX MATCH still wins in two situations:
- Older versions. If a client or manager is on Excel 2019 or earlier, XLOOKUP will not calculate for them.
- Two-way lookups you already know.
INDEX(range, MATCH(row), MATCH(column))is a pattern many analysts read at a glance.
Speed differences between them are not something you will notice on normal business sheets.
VLOOKUP approximate match: when you actually want it
Approximate match is not a bug. It is the right tool for bands and tiers, like discount levels by order size:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order from | Discount | Order | Discount | |
| 2 | 0 | 0% | 1,200 | 8% | |
| 3 | 500 | 5% | |||
| 4 | 1,000 | 8% | |||
| 5 | 2,500 | 10% |
For an order of 1,200:
=VLOOKUP(1200, A2:B5, 2, TRUE)
=XLOOKUP(1200, A2:A5, B2:B5, , -1)Both return 8%. Approximate match finds the largest value that is less than or equal to 1,200, which is 1,000. In XLOOKUP, match mode -1 means "exact match, or the next smaller item".
Approximate VLOOKUP only works when the first column is sorted smallest to largest. On product codes, IDs or names you almost never want it.
XLOOKUP multiple criteria
Sometimes one column is not enough to find the row. Here the target depends on region and quarter:
| A: Region | B: Quarter | C: Target | |
|---|---|---|---|
| 2 | North | Q1 | 40,000 |
| 3 | North | Q2 | 42,000 |
| 4 | West | Q1 | 35,000 |
| 5 | West | Q2 | 38,500 |
To get West's Q2 target:
=XLOOKUP(1, (A2:A5="West")*(B2:B5="Q2"), C2:C5)Result: 38,500. Each comparison gives TRUE or FALSE for every row. Multiplying them turns TRUE into 1 and FALSE into 0, so only the row where both are true becomes 1. XLOOKUP then looks for that 1.
The INDEX MATCH version uses the same trick:
=INDEX(C2:C5, MATCH(1, (A2:A5="West")*(B2:B5="Q2"), 0))In Excel 2019 and older you have to confirm it with Ctrl+Shift+Enter, because it is an array formula. In Microsoft 365 a normal Enter works.
Common mistakes
Leaving out FALSE in VLOOKUP. This is the classic mistake, and AI assistants make it often. =VLOOKUP(E2, A2:C6, 3) looks fine, but with no fourth argument VLOOKUP uses approximate match. If the codes are not sorted, prices can come back from the wrong rows. If a code is missing, you get a neighbour's price instead of an error. Type P-110 into E2: with approximate match on our sorted list it returns 9.60 (the price of P-105) instead of #N/A. When an AI hands you a VLOOKUP, read the last argument before anything else.
INDEX and MATCH ranges that start on different rows. =INDEX(C3:C7, MATCH(E2, A2:A6, 0)) has no error, but every result is shifted one row down. MATCH says "4th", INDEX gives the 4th cell of a range that starts a row later. To spot it, check that both ranges start and end on the same row numbers.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | Look up | Version | Result | |
| 2 | P-101 | Stapler | 12.50 | P-104 | AI's formula | 9.60 | |
| 3 | P-102 | Paper ream | 6.80 | Fixed | 3.90 | ||
| 4 | P-103 | Coffee filters | 4.25 | ||||
| 5 | P-104 | Dish soap | 3.90 | ||||
| 6 | P-105 | Floor cleaner | 9.60 |
The AI's version returns 9.60, the price of floor cleaner, for dish soap. No error, and 9.60 is a believable price.
Ranges that move when filled down. If you copy a lookup down a column, lock the table with $: $A$2:$C$6. Without it, the table slides down one row per copy and the last rows start returning #N/A. Our guide to percentage of total in Excel explains absolute references step by step.
Hiding every error with IFERROR. A blanket IFERROR(..., "") turns a typo in a code into an empty cell, and nobody notices. Prefer XLOOKUP's if_not_found or IFNA, which only catch "not found". More in IFERROR in Excel.
Lookup values that look equal but are not. "P-104 " with a trailing space or a number stored as text will not match. If one row fails and its neighbours work, check for spaces with =LEN(E2).
FAQ
Is XLOOKUP better than VLOOKUP?
For most work, yes. It defaults to exact match, can return columns to the left, does not depend on a typed column number and has a built-in "not found" argument. The only reason to stay with VLOOKUP is that the file must work in Excel 2019 or earlier, and in that case INDEX MATCH is the better fallback.
Why does my VLOOKUP return the wrong value?
Most often the fourth argument is missing or set to TRUE, so it does an approximate match. The second most common cause is a column number that no longer points at the right column after someone inserted or deleted a column.
Can XLOOKUP look up multiple criteria?
Yes. Multiply the conditions inside the lookup array, as in =XLOOKUP(1, (A2:A5="West")*(B2:B5="Q2"), C2:C5), and look up the value 1.
Is INDEX MATCH faster than XLOOKUP?
On typical business sheets you will not feel a difference. Choose based on readability and which Excel versions need to open the file.
What should I learn first for a data analyst interview?
Learn XLOOKUP and INDEX MATCH, and be ready to explain why VLOOKUP needs FALSE. Interviewers like to ask about all three. See our list of Excel interview questions for data analysts, or try the lookup drills on the practice page.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Prices pulled by the AI look odd” →