Day 7: XLOOKUP & Modern Lookup Functions

XLOOKUP is available in Excel 365 and Excel 2021. Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [not_found], [match_mode], [search_mode]). Main advantages: (1) Can look in any direction — no leftmost-column restriction. (2) Returns the exact column(s) you reference, not a number. (3) Has a built-in not_found parameter — no need for IFERROR wrapper. (4) Can return multiple columns at once. (5) Supports exact, approximate, and wildcard matching modes natively.

=XLOOKUP(A2, CustomerTable[CustomerID], CustomerTable[[Name]:[City]], "Not Found"). This single formula returns both Name and City columns — something VLOOKUP cannot do. The return_array references a range spanning two columns. If the CustomerID is not found, "Not Found" is returned instead of an error.

match_mode controls how XLOOKUP handles matching: 0 = exact match (default). -1 = exact match or next smaller. 1 = exact match or next larger. 2 = wildcard match (use * for any characters, ? for one character). Wildcard example: =XLOOKUP("*Delhi*", CityColumn, NameColumn,, 2) finds any city containing "Delhi".

search_mode controls search direction: 1 = search from first item (default). -1 = search from last item (finds the last match — useful for most recent transaction). 2 = binary search ascending (faster, requires sorted data). -2 = binary search descending. The search from last item (-1) mode is unique to XLOOKUP and solves a common problem elegantly: "find the most recent transaction for this customer" without needing additional filtering.

XMATCH is the modern replacement for MATCH, also available in Excel 365. Key improvements: supports wildcard matching, search from last item, and binary search. Syntax: =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]). In practice, XMATCH is used whenever you need the position of a value, and it is more flexible than MATCH for partial matches and reverse searches. Combined with INDEX, it is as powerful as XLOOKUP.