The
XLOOKUP for BigQuery
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.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/Aby default] The value to return if no match is found.match_mode: [OPTIONAL -0by 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 columnXLOOKUP("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/Aby default] The value to return if no match is found.match_mode: [OPTIONAL -0by default] The manner in which to find a match for thesearch_key.0is for an exact match.1is for an exact match or the next value that is greater than thesearch_key.-1is for an exact match or the next value that is lesser than thesearch_key.2is for a wildcard match.
search_mode: [OPTIONAL -1by default] The manner in which to search through thelookup_range.1is to search from the first entry to the last.-1is to search from the last entry to the first.2is to search through the range with binary search. The range needs to be sorted in ascending order first.-2is to search through the range with binary search. The range needs to be sorted in descending order first.
Notes
- If
result_rangeis 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.