XLOOKUP

Modern lookup function that replaces VLOOKUP and HLOOKUP. Searches a lookup array and returns a corresponding value from a return array. Includes built-in error handling and works in any direction.

Syntax

excel-mastery

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

Example

excel-mastery

=XLOOKUP(A2, ProductTable[ID], ProductTable[Price], "Not Found")

=XLOOKUP("*Smith*", CustomerTable[Name], CustomerTable[Email])

=XLOOKUP(1, (A:A=criteria1)*(B:B=criteria2), C:C)