Lookups - XLOOKUP()
XLOOKUP is the new version of lookup adding extra facility and being more reliable than LOOKUP, VLOOKUP and HLOOKUP.
Note that this formula will only work in the latest versions of Excel.
The XLOOKUP function works as follows:
=XLOOKUP(SearchValue, SearchRange, ResultRange, [NotFound],[MatchType], [SearchType])
SearchValue:
SearchRange:
ResultRange:
NotFound:
MatchType:
SearchType:
Value to be found
the Range to search for Value
the Range to find the Result
Result if SearchValue not found [Optional]
Exact or Approximate value [Optional]
Seach Ascending or Descending [Optional]
XLOOKUP operates in a way that is very similar to the LOOKUP function with the Optional Result Range used.
The Result Range does not need to be aligned with the Search Range, nor does it have to come after the Search Range. However, unlike with the LOOKUP function, the search and result ranges need to have the same orientation unless a further function is used.
In the example below, there are 4 formulae in the range H2:H5:
=XLOOKUP(G2,$A$1:$A$4,$B$1:$B$4)
=XLOOKUP(G3,$A$1:$A$4,$B$2:$B$5)
=XLOOKUP(G4,$A$1:$A$4,$B$1:$E$1)
=XLOOKUP(G5,$B$1:$B$4,$A$1:$A$4)
The first searches A1:A4 and finds a result in B1:B4.
Next searches A1:A4 for an offset result in B2:B5.
Searching A1:A4 for a horizontal result in B1:E1 produces an error.
Finally, search B1:B4 for result in A1:A4.
Another important difference from LOOKUP() is that XLOOKUP() requires the Search and Results range to be the same size. Different size ranges will produce an error.
VLOOKUP() gave users the option of finding an approximate, smaller, variable by default or the exact match if selected.
XLOOKUP() gives the user these options:
0 – Exact Match – Default
-1 – Exact Match or next smaller
1 – Exact Match or next larger
XLOOKUP() also adds the option for a result when exact match is not found.
=XLOOKUP(D2,$A$1:$A$4,$B$1:$B$4,”X”,0)
=XLOOKUP(D3,$A$1:$A$4,$B$2:$B$5,”X”,-1)
=XLOOKUP(D4,$A$1:$A$4,$B$1:$E$1,”X”,1)
=XLOOKUP(D5,$B$1:$B$4,$A$1:$A$4,”X”)
The first searches looks for an exact match.
As C is not found, the Not Found variable, “x”, is returned.
The next searches for the exact or next smaller. It finds B and returns 2.
Searching for the exact or next larger find D and returns 3.
Finally, searching with no match type marker defaults to an exact match search.
As C is not found it returns “x”.
An important aspect of XLOOKUP() is that the order of the search entries does not matter, even when looking for an approximate result.
The last variable in XLOOKUP() changes the sort direction. The default is 1, which searches in ascending order. If the marker is set to -1 then the search is in descending order.
=XLOOKUP(D2,$A$1:$A$4,$B$1:$B$4,”X”,1)
=XLOOKUP(D3,$A$1:$A$4,$B$2:$B$5,”X”,1,1)
=XLOOKUP(D4,$A$1:$A$4,$B$1:$E$1,”X”,1,-1)
The first formula finds A despite looking for the aproximate value. LOOKUP() and VLOOKUP() would have failed this.
The next formula sets the search to ascending and finds the first B as expected.
The final formula finds the last B by searching in descending order.
Finally, XLOOKUP() allows wildcard searches.
To use a wildcard the Match Type is set to 2.
Wildcards are ? to find one character or * to find multiple characters. So searching for “?arl” would find Karl and Carl.
In summary, to find the last Carl or Karl in a list and show ‘Not Found’ if neither are there would look something like:
=XLOOKUP(“?arl”, $A$1:$A$4, $B$1:$B$4,”Not Found”, 2, -1)
“?arl”
$A$1:$A$4
$B$1:$B$4
“Not Found”
2
-1
The Search Item.
Range to be searched.
Range of results.
Answer if search term not found.
Indicates wildcard search.
Indicates reverse search.
“?arl” – The Search Item.
$A$1:$A$4 – Range to be searched.
$B$1:$B$4 – Range of results.
“Not Found” – Answer if search term not found.
2 – Indicates wildcard search.
-1 – Indicates reverse search.