The XLOOKUP
function returns the values in the result range based on the position where a match was found in the lookup range. If no match is found, it returns the closest match.
XLOOKUP for BigQuery
Cross lookup. Returns the values in the data column at the position where a match was found in the search column.
Sample Usage
=XLOOKUP("Apple",table_name!fruit,table_name!price)
Syntax
XLOOKUP(search_key,lookup_range,result_range,missing_value,match_mode)
search_key
: The value to search for. For example,42
,"Cats"
, orB24
.search_column
: The column to consider for the search.result_column
: The column to consider for the result.missing_value
: [OPTIONAL -#N/A
by default] The value to return if no match is found.match_mode
: [OPTIONAL -0
by default] The manner in which to find a match for the search_key.0
: For an exact match.1
: For an exact match or the next value that is greater than the search_key.-1
: For an exact match or the next value that is lesser than the search_key.2
: For a wildcard match.
Tip: search_mode
isn’t supported in XLOOKUP for BigQuery.
Sample Usage
XLOOKUP("Apple", A2:A, E2:E)
to replace VLOOKUP("Apple", A2:E, 5, FALSE)
XLOOKUP("Price", A1:E1, A6:E6)
to replace HLOOKUP("Price", A1:E6, 6, FALSE)
XLOOKUP
where match column is to the right of the output column
XLOOKUP("Apple", E2:E7, A2:A7)
. The VLOOKUP
equivalent is VLOOKUP("Apple", {E2:E7, A2:A7}, 2, FALSE)
Syntax
XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode)
search_key
: The value to search for. For example,42
,"Cats"
, orB24
.lookup_range
: The range to consider for the search. This range must be a singular row or column.result_range
: The range to consider for the result. This range’s row or column size should be the same as thelookup_range
, depending on how the lookup is done.missing_value
: [OPTIONAL -#N/A
by default] The value to return if no match is found.match_mode
: [OPTIONAL -0
by default] The manner in which to find a match for thesearch_key
.0
is for an exact match.1
is for an exact match or the next value that is greater than thesearch_key
.-1
is for an exact match or the next value that is lesser than thesearch_key
.2
is for a wildcard match.
search_mode
: [OPTIONAL -1
by default] The manner in which to search through thelookup_range
.1
is to search from the first entry to the last.-1
is to search from the last entry to the first.2
is to search through the range with binary search. The range needs to be sorted in ascending order first.-2
is to search through the range with binary search. The range needs to be sorted in descending order first.
Notes
- If
result_range
is more than one row or column, then the output will be the entire row/column at the index a match was found in thelookup_range
.
Examples
Lookup table for all examples.
XLOOKUP
for Total amount sold with match_mode
and search_mode
omitted and missing argument specified.
XLOOKUP
for Total amount sold with match_mode = 0
and search_mode = 1
and -1
.
XLOOKUP
for Total amount sold with match_mode = 1
and -1
and search_mode
omitted.
XLOOKUP
using horizontal matching and returning an entire column.