XLOOKUP function

The XLOOKUP function returns the values in the result range based on the position where a match was found in the look-up range. If no match is found, it returns the closest match.

Sample usage

XLOOKUP("Apple, "Sheet2!A2:A", "Sheet2!E2:E") to replace VLOOKUP(A1, "Sheet2!A2:E", 5, FALSE)
XLOOKUP("Price", Sheet2!A1:E1, Sheet2!A6!E6) to replace HLOOKUP("Price", Sheet2!A1:E7, 6, FALSE)
XLOOKUP where match column is to the right of the output column
XLOOKUP(A1, Sheet2!E2:E7, Sheet2!A1:A7). The VLOOKUP equivalent is VLOOKUP(A1, {Sheet2!E2:E7, Sheet2!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" or B24.
  • lookup_range: The range to be considered for the search. This range must be a singular row or column.
  • result_range: The range to be considered for the result. This range's row or column size should be the same as the lookup_range, depending on how the look-up 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 the search_key.
    • 0 is for an exact match.
    • 1 is for an exact match or the next value that is greater than the search_key.
    • -1 is for an exact match or the next value that is lesser than the search_key.
    • 2 is for a wildcard match.
  • search_mode: [OPTIONAL – 1 by default] The manner in which to search through the lookup_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 the lookup_range.

Examples

Lookup table for all 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 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 = 0 and search_mode = 1 and -1.
 
XLOOKUP for total amount sold with match_mode = 1 and -1 and search_mode omitted.
XLOOKUP for Total amount sold with match_mode = 1 and -1 and search_mode omitted.
 
XLOOKUP using horizontal matching and returning an entire column.
XLOOKUP using horizontal matching and returning an entire column.

Related functions

Was this helpful?
How can we improve it?
true
Visit the Learning Center

Using Google products, like Google Docs, at work or school? Try powerful tips, tutorials, and templates. Learn to work on Office files without installing Office, create dynamic project plans and team calendars, auto-organize your inbox, and more.

Search
Clear search
Close search
Google apps
Main menu
Search Help Centre
false
false
true
35
false
false