XLOOKUP vs VLOOKUP vs INDEX MATCH: which should you use?

The short answer

• Use XLOOKUP if everyone who opens the file has Microsoft 365, Excel 2021 or later, or Google Sheets. It’s the simplest and safest.
• Use INDEX/MATCH if the file must work in Excel 2019 or older. It’s flexible and doesn’t break when columns move.
• Use VLOOKUP only for quick, simple lookups in older files — and always with FALSE as the last argument.

The example

All the formulas below look up a product code from cell A2 on a sheet called Products, where codes are in column A, names in column B and prices in column C.

VLOOKUP

VLOOKUP searches the first column of the range (Products!A:C) for the code and returns the value from the 3rd column of that range. The last argument, FALSE, means exact match.

Its weaknesses:

• It defaults to approximate match. Leave out FALSE and VLOOKUP may return a value from the wrong row without any error.
• It can only look right. The code must be in the first column of the range, so you can’t return a value from a column to its left.
• The column number is hard-coded. Insert a column between A and C and 3 now points at the wrong data.

INDEX/MATCH

MATCH finds the row where the code appears (the 0 means exact match), and INDEX returns the value in that row of the price column. Because the return column is referenced directly, inserting columns doesn’t break it, and the return column can be anywhere — including to the left of the lookup column.

It works in every version of Excel and in Google Sheets. The downside is readability: two nested functions, and MATCH also defaults to approximate matching if you leave out the 0.

XLOOKUP

XLOOKUP takes the value to find, the column to search and the column to return. It matches exactly by default, the fourth argument says what to show when nothing is found, and it can return several columns at once:

That returns both the product name and the price, spilling into two cells. XLOOKUP can also search from the bottom up — useful for “the most recent order for this customer” — with a sixth argument of -1.

Side-by-side comparison

Two-way lookups

To find the value where a row label and a column header meet — say the price for product A2 in the month named in B1 — combine INDEX with two MATCH functions:

Or nest two XLOOKUPs:

Lookups with two conditions

To find a price where both the product (F2) and the region (G2) match, multiply the two conditions so only rows where both are true equal 1, then look up 1:

In Excel 2019 and older, use INDEX/MATCH with the same idea and confirm the formula with Ctrl+Shift+Enter:

Common lookup mistakes

• Forgetting exact match — FALSE in VLOOKUP, 0 in MATCH.
• Relative ranges that move when copied. A2:C100 becomes A3:C101 in the next row. Use whole columns (A:C), absolute references ($A$2:$C$100) or an Excel table.
• Numbers stored as text on one side of the lookup, which makes matching values fail with #N/A. See how to convert text to numbers.
• Trailing spaces in either the lookup value or the lookup column. Wrap the lookup value in TRIM, and clean the column.
• Hiding real problems with IFERROR. Prefer XLOOKUP’s fourth argument or IFNA, which only catch “not found”. Our guide to Excel formula errors explains why.

Frequently asked questions