Excel guide · 3 min read

INDEX MATCH vs XLOOKUP

Quick answerUse XLOOKUP if everyone opening the file has Excel 365 or Excel 2021 or later: it is shorter, exact match by default, and has a built-in "not found" value. Use INDEX with MATCH when the file must work in Excel 2019 or older. Both return the same results, can look to the left, and keep working when you insert columns.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	D	F	G
1	ID	Product	Size	Price	Find ID	P-104
2	P-101	Hoodie	M	42	Product	Tee
3	P-102	Hoodie	L	44	Size	L
4	P-103	Tee	M	18
5	P-104	Tee	L	19.5
6	P-105	Cap	One size	16

The same lookup both ways

Find the price of product P-104. INDEX MATCH is two functions: MATCH finds the row where the ID sits, INDEX returns the value in that row of the price column. XLOOKUP does both in one: what to find, where to find it, what to return.

Note the 0 at the end of MATCH. Without it MATCH defaults to approximate match, which can return a wrong row on unsorted IDs. XLOOKUP defaults to exact match, so there is nothing to forget.

=XLOOKUP(G1,A2:A6,D2:D6)

Result: 19.5

P-104 is the large tee, priced 19.50. =INDEX(D2:D6,MATCH(G1,A2:A6,0)) gives the same 19.50.

When the value is not found

Both return #N/A for a missing ID. XLOOKUP takes a fourth argument with the message to show instead. INDEX MATCH needs a wrapper; use IFNA rather than IFERROR so typos in the formula still show up.

=XLOOKUP("P-999",A2:A6,D2:D6,"Not found")

Result: Not found

There is no P-999, so the result is Not found. =IFNA(INDEX(D2:D6,MATCH("P-999",A2:A6,0)),"Not found") does the same.

Looking to the left

VLOOKUP can only return columns to the right of the lookup column. Both alternatives can return any column. Here we search the product name and return the ID, which sits to its left.

=INDEX(A2:A6,MATCH("Cap",B2:B6,0))

Result: P-105

The Cap row has ID P-105. =XLOOKUP("Cap",B2:B6,A2:A6) returns the same.

Two conditions: product and size

Multiply the tests to build a list of 1s and 0s, then look for the 1. The pattern is the same in both. In Excel 2019 and older, the INDEX MATCH version must be confirmed with Ctrl+Shift+Enter.

=XLOOKUP(1,(B2:B6=G2)*(C2:C6=G3),D2:D6)

Result: 19.5

Only the large tee matches both, so the price is 19.50. =INDEX(D2:D6,MATCH(1,(B2:B6=G2)*(C2:C6=G3),0)) agrees.

Two-way lookups and the last match

For a row-and-column lookup, INDEX MATCH is still the clearest: one MATCH for the row, one for the column.

For the last match, XLOOKUP wins: set the sixth argument to -1 to search from the bottom up. INDEX MATCH has no simple equivalent.

=INDEX(A1:D6,MATCH(G1,A1:A6,0),MATCH("Price",A1:D1,0))

Result: 19.5

Row of P-104, column headed Price: 19.50.

Which one to pick

Pick XLOOKUP for new work in Excel 365 or 2021 and later: fewer arguments, safer defaults, built-in not-found handling, last-match search, and it can return a whole row of results at once.

Pick INDEX MATCH when the file goes to people on Excel 2019 or older, where XLOOKUP shows #NAME?. Speed is not a deciding factor for normal-sized sheets; both are fast.

Common mistakes

Leaving out the 0 in MATCH

MATCH(G1,A2:A6) defaults to approximate match and can return the wrong row. Always write MATCH(G1,A2:A6,0) for IDs and names.

Ranges of different sizes

The lookup and return ranges must have the same number of rows (A2:A6 and D2:D6). Mismatched sizes give #VALUE! in XLOOKUP and wrong rows in INDEX MATCH.

Sending an XLOOKUP file to older Excel

Excel 2019 and earlier show #NAME?. Use INDEX MATCH, or check which version your colleagues use first.

FAQ

Is XLOOKUP better than INDEX MATCH?

For Excel 365 and 2021+, yes in most cases: it is shorter and exact match by default. INDEX MATCH is still the right choice for files used in older versions.

Is INDEX MATCH faster than XLOOKUP?

The difference is negligible for everyday sheets. Choose based on compatibility and readability, not speed.

Does XLOOKUP work in Google Sheets?

Yes, Google Sheets added XLOOKUP in 2022. INDEX and MATCH work there too.

Can XLOOKUP return multiple columns?

Yes. If the return range is several columns wide, such as B2:D6, XLOOKUP spills the whole matching row in Excel 365.

Functions used

Related guides

Updated November 1, 2026