▦ Sheetova

XLOOKUP vs INDEX MATCH: Excel Examples and When to Use Each

Understand when to choose XLOOKUP or INDEX MATCH in Excel, with exact-match examples, version compatibility, and non-adjacent return fields.

XLOOKUP is concise for a single lookup in modern Excel. INDEX MATCH remains valuable for older versions and for worksheets that reuse one MATCH position across several separate return cells. Choose the function based on compatibility and how results fit your sheet.

One lookup, two formulas

To find the price for SKU E2, use =XLOOKUP(E2,A2:A100,B2:B100,"Not found") or =IFNA(INDEX(B2:B100,MATCH(E2,A2:A100,0)),"Not found"). MATCH needs its final 0 for an exact match.

Excel version support

XLOOKUP is supported in Microsoft 365 and Excel 2021 or later, but not Excel 2019 or older. INDEX and MATCH are supported in much older Excel versions, making them safer for legacy shared files.

One MATCH for several outputs

When a SKU should return price, location and stock into non-adjacent cells, calculate =MATCH(E2,A2:A100,0) once in a helper cell such as J2. Then use =INDEX(B2:B100,$J$2), =INDEX(C2:C100,$J$2) and =INDEX(D2:D100,$J$2) in the target cells.

When XLOOKUP is simpler

XLOOKUP can search right-to-left, return a fallback value, and search from last to first using its search_mode argument. It is usually easier to read when you need one result and everyone uses modern Excel.

Avoid common mistakes

Lookup and return arrays must have compatible sizes. With INDEX MATCH, include exact-match mode 0. With XLOOKUP, put the not-found value in the fourth argument, not the match-mode position.

Try the matching tools

Build a formula with the Excel formula builder, VLOOKUP generator, or XLOOKUP generator. Browse the formula guides for related problems.

Frequently asked questions

Does INDEX MATCH work in Excel 2019?

Yes. INDEX MATCH works in Excel 2019 and earlier, unlike XLOOKUP.

Can XLOOKUP return several columns?

In modern Excel it can return a multi-column array, but the output needs enough empty cells to spill.