Skip to content

All-Access: every lesson, no ads. $8.25€8.25US$8.25$4.08US$4.08$2.41US$2.41₹224.91MX$74.91 a month, billed yearly. Free to start. Cancel any time

See plans
All-Access, no ads: every lesson, course and template. $8.25€8.25US$8.25$4.08US$4.08$2.41US$2.41₹224.91MX$74.91 a month. + GST at checkoutSee the plans →

Functions

XLOOKUP vs INDEX MATCH: Which Is Better for Lookups?

By the LogicExcel Editorial Team5 min read891 wordsPractice this

Verdict: use XLOOKUP when everyone who opens the file has Excel 2021 or later, Microsoft 365 or Excel for the web, because one function does the whole job. Use INDEX and MATCH when the file has to open in Excel 2019 or older. Both look left and both give exact matches when set up correctly.

Ready to practice?

Apply what you just learned with interactive exercises.

Start Lesson 23

XLOOKUP and INDEX and MATCH solve the same problem: find a value in one column and return a value from the same row, in either direction. XLOOKUP is the newer, shorter way. INDEX and MATCH are the older, two-function way that works in every version.

The short answer

  • XLOOKUP is one function with the search column and the return column as two ranges. It is exact by default and has a place for your own not-found message.
  • INDEX and MATCH need two functions, and you must type 0 in MATCH to get an exact match. A missing value gives #N/A.
  • Both can look left, and both can do a two-way lookup.

Side by side

XLOOKUPINDEX and MATCH
Formula=XLOOKUP(lookup, lookup_array, return_array)=INDEX(return_range, MATCH(lookup, lookup_range, 0))
Functions to writeOneTwo
Exact matchDefaultType 0 in MATCH
When nothing matchesYour text in if_not_found#N/A, unless you wrap it in IFERROR
Looks left of the lookup columnYesYes
Two-way lookupAn XLOOKUP inside an XLOOKUPINDEX with two MATCH functions
Excel versionsExcel 2021 or later, Microsoft 365, Excel for the webAll

The same lookup, both ways

The price list again, with the SKU to find, A103, typed in E2.

ABC
1SKUProductPrice
2A101Notebook4.50
3A102Pen1.20
4A103Stapler8.90
5A104Folder2.10
6A105Scissors6.40
=XLOOKUP(E2,A2:A6,C2:C6,"Not found")
=IFERROR(INDEX(C2:C6,MATCH(E2,A2:A6,0)),"Not found")

Both return 8.90, and both return "Not found" for a SKU that is not on the list. The XLOOKUP formula says it in one function; the INDEX and MATCH formula needs IFERROR around it.

For a two-way lookup with products down column A and months across row 1, with the product in G1 and the month in G2:

=XLOOKUP(G1,A2:A6,XLOOKUP(G2,B1:D1,B2:D6))
=INDEX(B2:D6,MATCH(G1,A2:A6,0),MATCH(G2,B1:D1,0))

The inner XLOOKUP returns the column for the month, and the outer one picks the row for the product. The INDEX version does the same with two MATCH functions.

Which one should you use?

Choose XLOOKUP for new work in Excel 2021 or later, Microsoft 365 or Excel for the web. It is shorter, it is exact without being told, and it has a built-in message for a missing value.

Choose INDEX and MATCH when the file has to open in Excel 2019 or older, or when you are keeping an existing model that already uses them. A file that uses XLOOKUP shows a name error in older versions.

Keep both in your toolbox. Anyone who works with other people's workbooks will meet INDEX and MATCH, and anyone on a current Excel will want XLOOKUP. The older pair is also compared with VLOOKUP in INDEX MATCH vs VLOOKUP.

Practice the difference

Type each formula into a real sheet. The INDEX MATCH lookup lesson and the XLOOKUP lesson are marked as you go. If you want a path rather than one function, the Excel courses start free, and each one ends in a marked exam. More on each function: XLOOKUP and INDEX and MATCH.

FAQ

Is XLOOKUP better than INDEX MATCH?

For most new work, yes. XLOOKUP is one function, exact by default and has a built-in not-found message. INDEX and MATCH are the better choice when the workbook has to open in Excel 2019 or older, because XLOOKUP is not available there.

Is XLOOKUP replacing INDEX MATCH?

On a current version of Excel, XLOOKUP does the same job in a single function, so many people now write it first. INDEX and MATCH still work in every version and fill a large number of existing workbooks, so they remain worth knowing.

Can XLOOKUP do a two-way lookup like INDEX MATCH?

Yes. Put one XLOOKUP inside another: the inner one returns the column for the heading you want, and the outer one picks the row. INDEX with two MATCH functions does the same job and works in every version.

Why does my XLOOKUP show a name error?

The Excel you are using does not have XLOOKUP. It is in Excel 2021 or later, Microsoft 365 and Excel for the web, and not in Excel 2019 or older. Use INDEX and MATCH in files that older versions must open.

Keep going

Guides people read next.