Searches for a value in a range and returns the matching row or column from specified results range.
Syntax
 Copy Code
|
|---|
XLOOKUP( Any lookup, ReferenceOrArray lookupRange, ReferenceOrArray resultsRange, [Any ifNotFound], [Integer matchMode = 0], [Integer searchMode = 0] ) → Array |
Parameters
lookup | Required. The value to search for in the lookup range. |
lookupRange | Required. The table to be looked up. The source can be a reference to a range of cells or an array. |
resultsRange | Required. A range or array whose rows / columns to return. |
ifNotFound | Optional. A value to return if no matches found. |
matchMode | Optional. 0 (default) for exact match; -1 for exact match or next smaller item; 1 for exact match or next larger item. |
searchMode | Optional. 1 (default) to search in forward direction, or -1 to search in reverse direction. |
Remarks
This function return whole row or column from resultsRange and spills into adjacent cells. If they are occupied, the formula returns #SPILL error.
See Also
Function Reference
INDEX Function
MATCH Function
OFFSET Function
HLOOKUP Function