xlookup

XLOOKUP is a function in Microsoft Excel that searches for a value in a lookup_array and returns a corresponding value from a return_array. Introduced as a modern replacement for VLOOKUP, HLOOKUP, and LOOKUP, it supports vertical and horizontal lookups and can search in either direction.

Syntax: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The lookup_value is the value to search for. The lookup_array contains the values to search. The return_array contains the values to return. if_not_found is an optional value returned when no match is found. match_mode determines how matches are resolved: 0 for exact match (default), -1 for exact match or next smaller, 1 for exact match or next larger, -2 for wildcard matching. search_mode controls the search direction: 1 for first-to-last (default) and -1 for last-to-first.

XLOOKUP can return values from any position relative to the lookup_array, including to the left of the lookup_value. It supports wildcard characters when using the -2 mode. If a match is not found and if_not_found is not supplied, the function returns the #N/A error; otherwise it returns the specified value.

Availability and limitations: XLOOKUP is available in Excel for Microsoft 365 and Excel 2021 and later. It is not included in older Excel versions. The function aims to simplify lookups, improve robustness against column insertions, and reduce the need for nested formulas such as INDEX with MATCH.