Sign up for free
Functions

XLOOKUP

Search for a search key in a range and returns the value in the corresponding row or column, depending on specified parameters. Offers flexibility for searches in both rows and columns.

Parameter List

Syntax
XLOOKUP(search_key, lookup_range, return_range, 
[if_not_found]
, 
[match_mode]
, 
[search_mode]
)
search_key

The value to search for in the lookup_range. For example: "Super", 123, or A1.

lookup_range

The range of cells to search for the search_key. It must be a single row or column. For example: A1:A5 or C1:E1.

return_range

The range of cells to return a value from, corresponding to the position of the found search_key. It must have the same size and orientation as lookup_range.

[optional] if_not_found

The value to return if the search_key is not found. For example, "Not Found" or 0.

[optional] match_mode

The type of match to perform. Choose either:

  • 0: Exact match (default).

  • -1: Exact match or the next smaller item.

  • 1: Exact match or the next larger item.

  • 2: Wildcard match (supports *, ~ and ?).

[optional] search_mode

The order in which to search. Choose either:

  • 1: Search from first to last (default).

  • -1: Search from last to first.

  • 2: Perform a binary search in ascending order (range must be sorted).

  • -2: Perform a binary search in descending order (range must be sorted).

Examples

XLOOKUP("E02003", A1:A3, B1:B3, "Not found", 0, 1) returns "Robert Patel"