XLOOKUP Function in Excel

Learn how to use XLOOKUP in Excel to search ranges and return matching values without the limitations of VLOOKUP. Covers exact match, horizontal lookups, and missing values.

Syntax & Formula

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XLOOKUP replaces both VLOOKUP and HLOOKUP. It searches from left to right, right to left, top to bottom, and defaults to exact match without requiring FALSE.

Workplace Example

=XLOOKUP(D2, Staff[EmpID], Staff[Salary], "Not Found")

Common Mistakes to Avoid

Selecting arrays of unequal height or width. In XLOOKUP, the lookup_array and return_array must have the same dimension.

Practise this in the spreadsheet simulator