XLOOKUP function
Any more experienced user often uses the VLOOKUP function and is aware of its features and drawbacks. Therefore, the XLOOKUP function was created which can replace the VLOOKUP, HLOOKUP and LOOKUP functions. It also supports wildcard characters used to replace multiple (*) or single character (?) which makes it very flexible and probably your choice for some future spreadsheet calculations.
The syntax of this function is:
XLOOKUP (<value>,<array>,<return array>,[<not found>],[<comparison mode>],
[<search mode>])
and her arguments are:
- value – the value that we are trying to find
- string – the array that we search
- return array – the array from which function returns results
- not found – optional, the value displayed if no results were found
- comparison mode – optional, 0 for exact match (default), -1 for exact match or lower, +1 for exact match or higher, 2 for wildcard search
- search mode – optional, +1 for search from the beginning (default), -1 for search from the end, 2 for binary search (ascending order) and -2 for binary search (descending order).
The simplest way to use this function would be to write the formula:
= XLOOKUP (G1, B2: B21, D2: D21)
This means that we look for the value of cell G1 (“BLUE”) in the color column, and the first quantity found is returned as a result. We can modify the formula by adding text that will appear in the cell if no results were found:
= XLOOKUP (G4, B2: B21, D2: D21, “NOT FOUND”)
We could also search for colours whose name begins with the letters “BLU”. In this case it is necessary to write the formula:
= XLOOKUP (G7, B2: B21, D2: D21,, 2)
If we wanted Excel to search from the last element within the array we would write the formula:
= XLOOKUP (G10, B2: B21, D2: D21 ,,, – 1)
As you can see, the possibilities of this function are numerous. In one of the following “recipes” you will be able to see how it can still be used.