XLOOKUP

Searches a range for a key and returns the value of a given cell in the row/column found.
XLOOKUP works with both vertical and horizontal ranges.

Sample Usage

XLOOKUP(500,A1:Z10,2,FALSE)

Syntax

 XLOOKUP(value, range, index, [match_type], [search_type])

  • value  - The value you want to look up. 
  • range  - The range where the lookup value is located. 
  • index  - The row number in the range that contains the return value. 
  • match_type  - [OPTIONAL] Specifies the type of match. 
    • 0 = exact match (default)
    • -1 = exact match or next smallest
    • 1 = exact match or next larger
    • 2 = wildcard match
  • search_type  - [OPTIONAL] Specifies how Spreadsheet.com matches value with elements in range.
    • 1 search from first (default)
    • -1 = search from last
    • 2 = binary search ascending
    • -2 = binary search descending

 

Examples

XLOOKUP(500,A1:Z10,2,FALSE) looks for an exact match of the number 500 in the range A1:Z10 . If an exact match is found, the function returns the value corresponding to row 2 of the matched column. 

Notes

  • XLOOKUP will return #N/A if the lookup value is not found
  • The lookup_array must have a dimension compatible with the return_array argument, otherwise XLOOKUP will return a #VALUE! error.
  • If XLOOKUP is used between workbooks, both workbooks must be open, otherwise XLOOKUP will return a #REF! error.
  • If index is not between 1 and the number of rows in  range , #VALUE! is returned.
  • When searching for numeric or date values, make sure that the first row in the range is not sorted by text values. For example, correctly sorted numbers should appear as (1, 2, 10, 100) rather than (1, 10, 100, 2) as they would be if they were sorted as strings. Using an incorrect sort type may cause incorrect values to be returned.
  • You can also find matches using pattern strings that include wildcards. Currently, the use of asterisk (*) is supported.
    • An asterisk matches any sequence of characters.
    • To match an actual asterisk, type a tilde (~) before the character. For example: (~*).