What XLOOKUP does
XLOOKUP searches a column or row for a value and returns a result from the same position in a different column or row. It replaces VLOOKUP, HLOOKUP, and many INDEX/MATCH combinations with a single, more flexible function.
Key advantages over VLOOKUP: XLOOKUP can look left, does not require a column index number, defaults to exact match, and has a built-in error handler.
Practical examples
Look up a product price
=XLOOKUP(D2,A2:A100,C2:C100,"Not found")
Searches column A for the value in D2 and returns the corresponding price from column C. Returns "Not found" instead of an error when there is no match.
Look up with approximate match
=XLOOKUP(B2,E2:E10,F2:F10,,-1)
Finds the exact match or the next smaller value. Useful for tax brackets, shipping tiers, or grading scales.
Return multiple columns
=XLOOKUP(A2,Products[ID],Products[Name]:Products[Price])
XLOOKUP can return an entire row of results when the return_array spans multiple columns.
Common mistakes and notes
XLOOKUP replaces VLOOKUP in most cases
VLOOKUP only searches the leftmost column and requires a column index. XLOOKUP searches any column and returns from any column, making it more reliable when columns are added or reordered.
Default is exact match
Unlike VLOOKUP, which defaults to approximate match, XLOOKUP defaults to exact match. This prevents a common class of VLOOKUP errors.
Availability
XLOOKUP is available in Excel 365 and Excel 2021. It is not available in Excel 2019 or earlier.