5 simple Excel XLOOKUP examples
Let’s put the new XLOOKUP in Excel to the test with these 5 simple examples. The data below represents a list of serial numbers shipped in boxes to various companies in several countries. For simplicity, we keep the dataset simple and on the same screen.
The data set
Example 1: Basic XLOOKUP
In the first example, we will look for the country where a certain serial number is shipped.
the XLOOKUP function will look like this:
=XLOOKUP(F2,A:A,D:D,”not found”)
The function looks for the value in F2 “1125181” that can be found in the first column A:A and finds a result in the forth column D:D. In case the value is not found the function returns the text “not found”.
Here is the result of the function for the existing serial number and for a non existing serial number
Pretty cool. To do this before XLOOKUP you would have to use the function IF to evaluate the VLOOKUP function and to set the VLOOKUP as the TRUE value and “not found” as the FALSE value. Thanks XLOOKUP… much easier now
Example 2. Look to the left
In the second Example we will use the XLOOKUP function to look to the left. This is something that was impossible with VLOOKUP. In the example below we will try to find the first company that receives parts in a specific country. We will also use the value “not found” if the Country is not on the list.
As you notice, we are now looking for the value in the column F, we are trying to find it in the range D:D and return the company name from the column C:C. In the example, Canada and Italy are not in the list so they will return “not found” while USA will return the name of the first company “FarmFresh”
Example 3: multiple returns with 1 formula
This next example is mind blowing. Excel used to return a result of a formula in the cell where the formula was written. But XLOOKUP goes one cell forward. In the example below we want to return both the company and the Country where a specific serial number was shipped.
The cool part is that you write the formula in G2 and the result of the formula comes in G2 and H2. The formula in this case looks like this.
=XLOOKUP(F2,A:A,C:D,”not found”)
So basically look for the value in F2, into the array A:A and return values (plural) found in the range C:D. Notice that the return value stretch over two columns so the result will have to stretch over 2 columns. No formula is written in the column H. However the result of the formula will fill the cell in column G and H.
Same goes if we expand the return range to 3 columns.
This is an amazing feature of XLOOKUP. Imagine that you can bring a whole list of fields from another sheet into your sheet with one single formula. It can be useful to do that if you want to do a pivot table on the entire data. What before took 3 vlookups, not it takes one single formula. And the amazing part is that you can search for the value in any direction. Left to Right, Right to Left and even search for a value in the middle. See below how we search for all the fields related to the value in F2. We are asking XLOOKUP to bring all the fields in A:D if it finds the value from F2 in column C:C. Pretty slick.
Example 4: Find the closest value
In the next example we’ll search using the second optional parameter [match mode]. Here is how this parameter is defined.
[match_mode]
- 0 – Exact match, where the lookup_value should exactly match the value in the lookup_array. This is the default option.
- -1 – Looks for the exact match, but if it’s found, returns the next smaller item/value
- 1 – Looks for the exact match, but if it’s found, returns the next larger item/value
- 2 – To do partial matching using wildcards (* or ~)
So let’s add a match mode parameter to this XLOOKUP with the value of -1
=XLOOKUP(F2,A:A,B:D,”nothing”,-1)
If the value exists, then the function returns the expected columns:
But let’s try to look for a value larger than all the values in A:A
In this case, the value is not found but because of the parameter [match_mode] = -1 XLOOKUP will return the next smaller item from 99999999. in this case that item is the last row of our dataset.
If we change [match_mode] to +1 and search for a very small value…
… we’ll get the value in the first row because [match_mode]=1 will return the next larger item from 23
Example 5: Search from the bottom
the last simple example for XLOOKUP is to start searching from the bottom up. The default mode, same as VLOOKUP is to search from the top to the bottom row. However, with the new parameter [search_mode] you can now search the other way.
[search_mode]
- 1 – This is the default option where the function starts looking for the lookup_value from the top (first item) to the bottom (last item) in the lookup_array
- -1 – Does the search from bottom to top. Useful when you want to find the last matching value in the lookup_array
- 2 – Performs a binary search where the data needs to be sorted in ascending order. If not sorted, this can give error or wrong results
- -2 – Performs a binary search where the data needs to be sorted in descending order. If not sorted, this can give error or wrong results
Here’s how it works
the formulas are like this:
=XLOOKUP(F2,D:D,A:D,”nothing”,,1)
=XLOOKUP(F2,D:D,A:D,”nothing”,,-1)
Notice that these formulas will search for the same value “USA” but the first formula will find the first from the top while the second formula will find the first from the bottom.
Here are some YouTube videos that explain XLOOKUP.
