Blog / Lookups
VLOOKUP vs XLOOKUP vs INDEX MATCH: which should you use?
3 November 2026 ยท 2 min read
A plain comparison of Excel's three main lookup methods, with the same example solved each way and a simple rule for choosing.
Lookups are the skill employers test most, and Excel gives you three ways to do them. They can all find a price from a product code, but they behave differently once your data gets messy. Here is the same task solved three ways.
The task: a product list has codes in column A, names in column B and prices in column C. Find the price for the code typed in F2.
VLOOKUP
=VLOOKUP(F2, A2:C100, 3, FALSE)
VLOOKUP searches the first column of the range and returns the column number you give it, here 3 for price. The final FALSE means an exact match; leave it out and VLOOKUP may quietly return the wrong row.
Its limits: it can only return columns to the right of the search column, and if someone inserts a column inside the table, the number 3 no longer points at the price.
INDEX and MATCH
=INDEX(C2:C100, MATCH(F2, A2:A100, 0))
MATCH finds which row the code is in, and INDEX returns that row from the price column. Because you point at the price column directly, inserting columns never breaks it, and the price column can be on either side of the codes. It works in every version of Excel.
XLOOKUP
=XLOOKUP(F2, A2:A100, C2:C100, "Not found")
XLOOKUP reads like the question: look for F2 in the codes and bring back the price. It matches exactly by default, works in any direction, and has a built-in message for missing values. It is available in Excel 2021 and Microsoft 365.
Which should you learn?
- Use XLOOKUP whenever everyone who opens the file has Microsoft 365 or Excel 2021. It is the clearest and hardest to get wrong.
- Use INDEX and MATCH when files are shared with people on older versions, or when you need a two-way lookup by row and column.
- Know VLOOKUP because you will meet it in other people's spreadsheets and in job tests.
In interviews, being able to explain *why* you chose one is worth as much as writing it. Try all three on the same data in our lookup lessons, and see the guides for VLOOKUP, XLOOKUP and INDEX & MATCH.
Practise it: XLOOKUP in depth
Type the formulas yourself in a real spreadsheet, with instant feedback on every answer. Your first five lessons are free.