XLOOKUP pitfall 1: Defaults to exact match: no need to remember FALSE?

Defaults to exact match: no need to remember `FALSE`. The 4th arg is the not-found return value, NOT a sort flag.

XLOOKUP pitfall 2: The 6th arg sets search direction; pass -1 to find the LAST match instead of the first?

The 6th arg sets search direction; pass `-1` to find the LAST match instead of the first.

Returns `#N/A` if not found and no default supplied. Pass an empty string or sentinel as the 4th argument.

Lookupmedium

The modern replacement for VLOOKUP. Searches any column, returns from any column.

Syntax

=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode])

Arguments

Argument	Description
search_key	The value to search for.
lookup_range	The range to search through.
result_range	The range from which to return a result.

Example

=XLOOKUP("Acme Co", A:A, C:C, "Not found")

Paste this into Google Sheets to run it. The live playground on this site covers 24 of the 61 functions, and XLOOKUP isn't one of them yet.

Defaults to exact match: no need to remember FALSE. The 4th arg is the not-found return value, NOT a sort flag.
The 6th arg sets search direction; pass -1 to find the LAST match instead of the first.
Returns #N/A if not found and no default supplied. Pass an empty string or sentinel as the 4th argument.

Related formulas

Used by