Searches a vertical or horizontal one-dimensional range for a key and returns the matching row or column range of the return array. XLOOKUP works with both vertical and horizontal ranges.
Sample Usage
XLOOKUP("product",A1:A10,B1:B10,"Not Found")
Syntax
XLOOKUP(value, range, return_array, [not_found_value], [match_type], [search_type])
- value - The value you want to look up.
- range - The range where the lookup value is located.
- return_array - The range that contains the return value.
- not_found_value - [OPTIONAL] Return value if match not found
- 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("product",A1:A10,B1:B10,"Not Found") looks for an exact match of the text string product in the range A1:A10. If an exact match is found, the function returns the value corresponding in the range B1:B10 of the matched row. If an exact match is not found, the function returns "Not Found".
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 doing binary search over numeric or date values, make sure data in the lookup 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: (~*).