Use XLOOKUP for most new workbooks because it reads clearly, looks in either direction, and supports an explicit not-found result. Use INDEX with MATCH when an older Excel version must open the file or when an existing model already uses that pattern consistently.
- Default to exact matching for IDs, product codes, and employee numbers.
- Keep the lookup key unique or decide deliberately which duplicate should win.
- Check workbook compatibility before replacing INDEX-MATCH in a shared file.
Why XLOOKUP is easier to read
XLOOKUP separates the lookup array from the return array. A formula can say: find this product code in the Product column and return the matching price from the Price column. The return column may be to the left or right of the lookup column.
The optional not-found argument lets the workbook say Missing product instead of showing #N/A. That is useful when a missing code is expected, but the message should still make the missing record visible.
When INDEX-MATCH still makes sense
INDEX returns a value by position, while MATCH finds the position of a key. Together they create a flexible lookup and work in older Excel versions. The pattern is also familiar in many established financial and operational models.
- XLOOKUP exact match: =XLOOKUP(A2,Products[Code],Products[Price],"Missing").
- INDEX-MATCH exact match: =INDEX(Products[Price],MATCH(A2,Products[Code],0)).
- MATCH uses 0 for an exact match in this pattern.
Return a department from an employee ID
An Employees table contains ID, Name, and Department. The requested ID is in B2.
- Confirm that Employee ID is unique in the source table.
- Enter =XLOOKUP(B2,Employees[ID],Employees[Department],"ID not found").
- Test one valid ID and one invalid ID before copying the formula.
Common mistakes
- Using approximate matching on identifiers that require an exact match.
- Ignoring duplicate lookup keys and assuming the returned row is unique.
- Returning a blank for missing records, which makes data quality problems invisible.
- Introducing XLOOKUP when recipients must use an Excel version that does not support it.
Try one
Why is XLOOKUP usually clearer than VLOOKUP when the return column is left of the key?
XLOOKUP names separate lookup and return arrays, so it can return from either direction without rearranging the table.
Sources
- Microsoft Support: XLOOKUP functionOfficial syntax and matching behavior for XLOOKUP.