VLOOKUP vs XLOOKUP vs INDEX and MATCH: which one to use in an Excel test
Three ways to look a value up in Excel, the one each job needs, and what an interviewer is checking when they ask.
avdhoot-fulsundar
Facts checked by hand on November 11, 2026
Use XLOOKUP if your Excel is Microsoft 365 or 2021 and later. Use INDEX with MATCH if the file has to open in an older version or you need a two-way lookup. Use VLOOKUP only for a plain lookup to the right, and because tests still ask for it. All three return 89 for the price of P-104 in the table below.
On this page
Every Excel test eventually asks you to bring a value in from another table. There are three tools for it, they overlap a lot, and picking the wrong one costs you either a wrong answer or a formula the interviewer's Excel cannot open. This is how they differ, on one small table.
It is the stationery catalog used across the function guide:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Product | Category | Price |
| 2 | P-101 | Stapler everyone borrows | Office | 4.95 |
| 3 | P-102 | A5 notebook | Stationery | 2.30 |
| 4 | P-103 | Markers (12 colors) | Stationery | 6.80 |
| 5 | P-104 | Office chair | Furniture | 89.00 |
| 6 | P-105 | Desk lamp | Furniture | 24.50 |
What does the same lookup look like in each one?#
The question: what is the price of P-104?
| Function | Formula | Result |
|---|---|---|
| VLOOKUP | =VLOOKUP("P-104",A2:D6,4,FALSE) | 89 |
| XLOOKUP | =XLOOKUP("P-104",A2:A6,D2:D6) | 89 |
| INDEX and MATCH | =INDEX(D2:D6,MATCH("P-104",A2:A6,0)) | 89 |
The difference is in how each one is told where to look. VLOOKUP gets one range and a column number, so the answer depends on counting columns. XLOOKUP gets two ranges, the one it searches and the one it answers from. INDEX and MATCH split the job in two: MATCH finds the position and INDEX brings back what sits there.
When does each one break?#
Three situations decide it.
The answer is to the left of the key. Suppose you have the product name and want its code. The code is in column A, the name in column B.
=VLOOKUP("Office chair",A2:D6,1,FALSE)fails: VLOOKUP only looks in the first column of the range, and "Office chair" is in the second.=XLOOKUP("Office chair",B2:B6,A2:A6)returns P-104.=INDEX(A2:A6,MATCH("Office chair",B2:B6,0))returns P-104.
You insert a column. With =VLOOKUP("P-104",A2:D6,4,FALSE), the 4 is a count. Insert a column inside the range and the 4 now points at the wrong column; Excel does not warn you. XLOOKUP and INDEX with MATCH point at whole ranges, so they follow the data.
The file opens in an older Excel. XLOOKUP exists in Microsoft 365 and Excel 2021 and later. In Excel 2019 and earlier it shows as #NAME?. VLOOKUP and INDEX with MATCH work everywhere.
Which one should you use?#
| VLOOKUP | XLOOKUP | INDEX and MATCH | |
|---|---|---|---|
| Works in older Excel | Yes | No | Yes |
| Looks to the left | No | Yes | Yes |
| Survives inserted columns | No | Yes | Yes |
| Exact match by default | No (type FALSE) | Yes | No (type 0) |
| Two-way lookup | No | Nesting two | Yes |
| Typed functions | 1 | 1 | 2 |
My rule: XLOOKUP when you control the version, INDEX with MATCH when you do not, VLOOKUP when you are reading someone else's file. If you have to choose one to learn well, learn INDEX with MATCH: it also explains why the other two behave as they do.
What is an interviewer checking when they ask for a lookup?#
Usually two things, and neither is the function name.
First, whether you ask which direction the data runs. "Bring in the code from the product name" is a left lookup on purpose; it separates people who memorized VLOOKUP from people who understand lookups.
Second, whether you handle the missing value. A code that is not in the table returns #N/A. Wrapping it, as in =IFERROR(XLOOKUP("P-999",A2:A6,D2:D6),"Not found"), returns Not found. See IFERROR for when that hides a real problem instead of a missing code.
A test question looks like this:
A lookup returns the right price for P-104 but the wrong price for P-105. The formula is
=VLOOKUP(A10,A2:D6,4). What is missing? a) Quotes around A10 · b) FALSE as the last argument · c) A sorted table · d) Column 5
Answer: b. Without it VLOOKUP does an approximate match, which only behaves on a sorted list.
Practice the three#
The English exercises are being finished. Until they open, copy the catalog into a blank sheet and write each of the three formulas above, then the left lookup, then insert a column and see which ones break. The function pages have more variants: VLOOKUP, XLOOKUP and INDEX and MATCH.
Sources: Microsoft: VLOOKUP function, XLOOKUP function, INDEX function. Results calculated with Formulo's engine.
Frequently asked questions
- 01Is XLOOKUP better than VLOOKUP?
- For most jobs, yes: it looks to the left, defaults to an exact match and keeps working when you insert a column. VLOOKUP is the one that works in every version, so you still need to read it.
- 02Which lookup should I type in a job interview test?
- The one that does what the task needs. If the task asks you to bring in a value from a column to the left of the key, VLOOKUP cannot do it, and XLOOKUP or INDEX with MATCH can.
- 03Why does my lookup return the wrong value without an error?
- VLOOKUP with the last argument left out does an approximate match. Type 0 (or FALSE) so it only accepts the exact value.
- 04Do the Spanish names change?
- Yes: BUSCARV, BUSCARX, and INDICE with COINCIDIR. The arguments are the same, separated by semicolons in most Spanish installations.
Practice your first formula now
No sign-up. It takes less than two minutes.