XLOOKUP Function 2

Freeintermediate 15 min4 tasks0/90 pts

XLOOKUP has more tricks: the 5th argument match_mode finds the next larger (1) or next smaller (-1) item, and the 6th search_mode can search from the last match backwards (-1). Lookup and return ranges must be the same size.

Syntax

XLOOKUP(value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Example: =XLOOKUP("Ana",A2:A7,B2:B7,"none",0,-1) returns the latest order for Ana.

Common mistakes

Lookup and return ranges of different sizes or offset by a row.
Forgetting the "if not found" argument before match_mode.

XLOOKUP Function XLOOKUP & Wildcards