INDEX MATCH vs XLOOKUP: Is INDEX MATCH Still Worth Learning?

The short answer to INDEX MATCH vs XLOOKUP: XLOOKUP is the easier formula for new files on Excel 2021, Excel 2024 or Microsoft 365, but INDEX MATCH is still worth learning because it works in every version and fills older workbooks. Both can look left and both survive inserted columns. This guide is for anyone deciding which lookup to practise before a job or an Excel test.

INDEX MATCH vs XLOOKUP at a glance#

XLOOKUP does the same job as INDEX MATCH in one function, with a few extras, but only in newer Excel.

How the syntax differs#

INDEX MATCH asks two questions: MATCH finds the position, then INDEX reads that position. XLOOKUP asks both at once.

=INDEX(array, MATCH(lookup_value, lookup_array, 0))
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

In INDEX MATCH, the 0 at the end of MATCH means exact match. If you leave it out, MATCH uses 1, an approximate match that expects the list sorted from smallest to largest. XLOOKUP is exact unless you set match_mode. The INDEX function guide and the XLOOKUP function guide cover every argument.

The same lookup solved both ways#

Here is a small stock list. The product names are in column A, to the left of the SKUs in column B.

Task 1: find a product name by SKU (a left lookup)#

With TE-215 in G2, both formulas return Chai Tea 50 bags:

=INDEX(A2:A7,MATCH(G2,B2:B7,0))
=XLOOKUP(G2,B2:B7,A2:A7)

MATCH finds TE-215 at position 4, and INDEX returns the 4th product. XLOOKUP searches the SKUs and returns the matching product directly. Both point at real ranges, so both keep working if someone inserts a column.

Task 2: handle a SKU that isn't there#

If G2 holds TE-999, there is no match. INDEX MATCH returns #N/A unless you wrap it. XLOOKUP has the message built in:

=IFNA(INDEX(A2:A7,MATCH(G2,B2:B7,0)),"Not found")
=XLOOKUP(G2,B2:B7,A2:A7,"Not found")

Both show Not found. IFNA catches only #N/A, so a real mistake such as #REF! still shows. Our guide to IFERROR vs IFNA explains why that matters.

Task 3: a two-way lookup#

G2 holds Green Tea 50 bags and G3 holds the header In Stock. You want the value where that row and column meet:

=INDEX(A2:D7,MATCH(G2,A2:A7,0),MATCH(G3,A1:D1,0))
=XLOOKUP(G2,A2:A7,XLOOKUP(G3,A1:D1,A2:D7))

Both return 220. In the XLOOKUP version, the inner XLOOKUP finds the In Stock header and returns that whole column, D2:D7. The outer XLOOKUP then finds the product in it. Many people find the INDEX version easier to read here, because the row and the column are two clear parts.

Task 4: find the last match#

You want the last Tea product in the list. XLOOKUP can search from the bottom up with search_mode set to -1:

=XLOOKUP("Tea",C2:C7,A2:A7,,0,-1)

This returns Chai Tea 50 bags. The two commas skip if_not_found, 0 is an exact match, and -1 searches last to first. MATCH always returns the first match, so INDEX MATCH needs a much longer formula for this.

When to use INDEX MATCH#

Use INDEX MATCH when the file must work everywhere or already uses it. It is the right choice when:

• The file goes to people on Excel 2019 or older. XLOOKUP can't calculate there and shows #NAME?.
• You're editing a workbook that already uses INDEX MATCH. One style is easier for colleagues to follow.
• An interviewer asks about it. "Explain INDEX MATCH" is a common question; see our Excel interview questions with answers.
• You need a position, not a value. MATCH alone tells you where something is, and INDEX alone returns the item at a position, such as the 3rd row of a list.

When to use XLOOKUP#

Use XLOOKUP for new files when everyone has Excel 2021, Excel 2024 or Microsoft 365. It is the better choice when:

• You want a short, readable formula that a colleague can follow.
• You want a "not found" message without wrapping the formula in IFNA.
• You need the last match, such as the most recent order for a customer.
• You want several columns back. =XLOOKUP("CF-102",B2:B7,C2:D7) returns the category and the stock, Coffee and 65, side by side.
• You need a band lookup on unsorted data. Match modes -1 (next smaller) and 1 (next larger) don't need sorting.

Analyst roles often expect XLOOKUP; see the Excel skills every data analyst needs.

Common mistakes with INDEX MATCH and XLOOKUP#

These slips cause most wrong lookups in both formulas:

1. Leaving out MATCH's 0. =INDEX(A2:A7,MATCH(G2,B2:B7)) uses an approximate match. On unsorted SKUs it can return the wrong product with no error.
2. Ranges that start on different rows. MATCH(G2,B1:B7,0) counts the header, so INDEX returns the row below the right one. Start every range on row 2.
3. Ranges of different sizes in XLOOKUP. =XLOOKUP(G2,B2:B7,A2:A8) returns #VALUE!, because the lookup and return ranges must be the same height.
4. Not locking ranges before filling down. Use $B$2:$B$7 (press F4 on Windows or ⌘T on a Mac) before you fill with Ctrl+D (⌘D on a Mac).
5. Sharing XLOOKUP with someone on older Excel. Check which version the file's readers, or your test, will use.
6. Expecting a case-sensitive match. Neither one is case-sensitive: te-215 finds TE-215.

Is INDEX MATCH still worth learning?#

Yes, for three practical reasons. Plenty of existing workbooks use it, so you need to read and fix it. Some workplaces and test platforms run older Excel, where XLOOKUP doesn't exist. And knowing that MATCH returns a position helps you understand newer tools, such as XMATCH and two-way lookups. It takes one short lesson to learn.

There is also a middle path. On Excel 2021, Excel 2024 or Microsoft 365, you can swap MATCH for XMATCH, the modern version that uses an exact match by default:

=INDEX(A2:A7,XMATCH(G2,B2:B7))

This returns Chai Tea 50 bags for TE-215, like Task 1, without the 0. It keeps the INDEX MATCH pattern, which is handy when a team already knows it, but it has the same version limits as XLOOKUP. If you need the file to work in Excel 2019 or older, stay with MATCH and its 0.

The same "older and universal vs newer and simpler" choice comes up elsewhere in Excel, for example with nested IF vs IFS and SUMIF vs SUMIFS.

Our recommendation#

Learn both, and write XLOOKUP by default when everyone has Excel 2021, Excel 2024 or Microsoft 365. XLOOKUP is shorter, exact by default and handles "not found" and last-match searches for you. Keep INDEX MATCH for files that must work in any version and for interviews, where it shows you understand how lookups work.

In short: XLOOKUP for new files on modern Excel, INDEX MATCH when compatibility matters.

Practise it#

Try the XLOOKUP exercise on this page, then solve the same left lookup with INDEX MATCH. Work through the INDEX and MATCH lesson and the XLOOKUP lesson in a live spreadsheet to get fast with both.