Look up values in range or array
=XLOOKUP(lookup, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])| Parameter | Description |
|---|---|
lookup |
The lookup value. |
lookup_array |
The array or range to search. |
return_array |
The array or range to return. |
if_not_found |
[optional] Value to return if no match found. |
match_mode |
[optional] 0 = exact match (default), -1 = exact match or next smallest, 1 = exact match or next larger, 2 = wildcard match, 3 = regex match. |
search_mode |
[optional] 1 = search from first (default), -1 = search from last, 2 = binary search ascending, -2 = binary search descending. |
By default, XLOOKUP will perform an exact match. In the example below, XLOOKUP is configured to retrieve the Sales amount from column E based on an ex
=XLOOKUP(H4,B5:B9,E5:E9)To enable an approximate match, provide a value for the match_mode argument. In the example below, XLOOKUP is used to calculate a discount based on qu
=XLOOKUP(E5,B5:B9,C5:C9,,-1)XLOOKUP can return more than one value at the same time (i.e., an array of values) with one formula. The example below shows how XLOOKUP can be used t
=XLOOKUP(B5,B8:B15,C8:E15)XLOOKUP can perform a two-way lookup by nesting one XLOOKUP inside another. In the example below, the "inner" XLOOKUP retrieves an entire row (all val
=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))When XLOOKUP can't find a match, it returns the #N/A error, like other match functions in Excel. Unlike the other match functions, XLOOKUP supports an
=XLOOKUP(H4,B5:B9,E5:E9,"Not found")XLOOKUP supports wildcards to enable partial match lookups. Set the match_mode argument to 2 to enable wildcards in XLOOKUP. In the example below, XLO
=TRANSPOSE(XLOOKUP("*"&G4&"*",data[Title],data,,2))