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.