Explanation
In this example, the goal is to look up amounts for 1000 invoice numbers in a table that contains 1 million invoices. The catch is that not all of the 1000 invoice numbers exist in the source data. In fact, most of the invoice numbers do not appear in column B . This means we need to take care to configure XLOOKUP to use an exact match , and exact match lookups on large data sets can be painfully slow. However, because the data is sorted by invoice number, we can enable XLOOKUP’s binary search mode, which is optimized for speed. The result is a much faster formula.
XLOOKUP exact match mode
When you use XLOOKUP in “exact match mode” on a large set of data, it can slow down the calculation time in a worksheet. The general form for an exact match lookup with XLOOKUP looks like this:
=XLOOKUP(A1,lookup_array,return_array,,0) // exact match
The 0 for match_mode specifies an exact match. The reason XLOOKUP is slow in this mode is that there is no requirement that the lookup values be sorted. As a result, XLOOKUP must check every record in the data set until a match is found, or not. This is sometimes referred to as a linear search .
XLOOKUP binary search mode
To enable binary search mode with XLOOKUP, data must be sorted in ascending or descending order. If values are sorted in ascending order , use the value 2 to enable binary search. If values are sorted in descending order , use the value -2:
=XLOOKUP(A1,lookup_array,return_array,,0,2) // exact match binary A-Z
=XLOOKUP(A1,lookup_array,return_array,,0,-2) // exact match binary Z-A
Note in the formulas above, we are not providing a value for the if_not_found argument. This means XLOOKUP will simply return a #N/A error if a value is not found, like other lookup formulas. With binary search enabled, XLOOKUP will run very fast.
For a complete XLOOKUP overview with many examples, see How to use XLOOKUP .
The solution
In the worksheet shown, the goal is to look up each of the 1000 invoice numbers that appear in column E. If we find the invoice number in the Excel Table named data , we want to return the amount. If we don’t find the invoice number, we don’t want to show anything. To solve this problem, we use a formula like this in cell D5:
=XLOOKUP(E5,data[Invoice],data[Amount],"",0,2)
For the if_not_found argument, we provide an empty string (""). To require an exact match, we use 0 for match_mode . To enable XLOOKUP’S binary search mode, we use 2 for search_mode . As the formula is copied down column E, it returns the amount for invoice numbers that exist, and an empty string ("") if the invoice number is not found. XLOOKUP returns results very quickly because binary search mode is enabled and data is sorted by invoice number in ascending order.
INDEX and XMATCH option
Because the XMATCH function has a binary search option, it is possible to write an INDEX and MATCH formula that also calculates very quickly. Like XLOOKUP, the search_mode argument must be 2:
=INDEX(data[Amount],XMATCH(E5,data[Invoice],0,2))
Unlike XLOOKUP, there is no built-in option to handle errors, so you would need to wrap the formula above in IFERROR or IFNA to trap #N/A errors and return an empty string.
Notes
- This approach is overkill unless lookup performance is an issue.
- VLOOKUP does not have a binary search mode, but there is a workaround .
- See XLOOKUP vs INDEX and MATCH for a detailed comparison.
Explanation
In this example, the goal is to perform a case-sensitive lookup on the color in the “Color” column. In other words, a lookup value of “RED” must return a different result from a lookup value of “Red”. By default, Excel is not case-sensitive and this applies to standard lookup formulas like VLOOKUP , XLOOKUP , and INDEX and MATCH . These formulas will simply return the first match, ignoring case. For example, if we use a standard XLOOKUP formula like this:
=XLOOKUP("RED",data[Color],data[Qty]) // returns 17
XLOOKUP will match “Red” in row 3 of the table and return 17, even though the lookup value is “RED” in uppercase.
We need a way to get Excel to compare case. The EXACT function is perfect for this job, but the way we use it is a little different. Instead of comparing one text value to another, we compare one text value to many values. In a nutshell, we use the EXACT function to generate an array of TRUE and FALSE values, then use the XLOOKUP function to locate the first TRUE in the array.
Like many advanced formulas in Excel, this approach requires you to imagine what the formula is doing, even though the process is largely invisible. There is no shortcut to mastering these ideas, you just have to practice :)
Background reading
This article assumes you are familiar with the XLOOKUP function and Excel Tables. If not, see:
- Excel Tables - introduction and overview
- Basic XLOOKUP example - 3-minute video
EXACT function
Working from the inside out, to give XLOOKUP the ability to match case, we use the EXACT function like this:
EXACT(data[Color],F5)
Since there are 12 values in the Color column, the EXACT function will return an array with 12 TRUE and FALSE results like this:
{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}
Notice the position of the first TRUE (5) corresponds to row 5 in the table, where Color is “RED”. EXACT returns TRUE again for “RED” in row 9 of the table. Every other result is FALSE, including “Red” in row 3.
XLOOKUP with EXACT
As explained above, the EXACT function creates an array of TRUE and FALSE values, one for each value in the Color column. This array is returned directly to the XLOOKUP function as the lookup_array argument. Now we need to give XLOOKUP an appropriate lookup_value . Instead of looking for “RED” (the original lookup value), we provide the value TRUE. This may seem a bit strange, but remember that when the EXACT function runs, it returns an array of TRUE/FALSE values. The original values are gone and thus we need to look for TRUE and not “RED”.
Finally, we need to provide a return_array . This is the column that contains the values we want as a result. In this example, return_array is the last column in the table, data[Qty]. The final formula in cell G5 looks like this:
=XLOOKUP(TRUE,EXACT(data[Color],F5),data[Qty]) // returns 10
In summary, the EXACT function compares the value in F5 with every value in data[Color], generating an array of TRUE and FALSE values. This array is returned to XLOOKUP as the lookup_array . XLOOKUP locates the TRUE at position 5 in the array and returns the value at row 5 in the Qty column, 10 as a final result.
Notice that XLOOKUP matches on the first TRUE and not the second and last TRUE. This is standard behavior for Excel’s lookup functions when there is more than one match.
Alternate syntax
You may sometimes see an alternate syntax like this:
=XLOOKUP(1,--EXACT(data[Color],F5),data[Qty]) // returns 10
The behavior of this formula is the same. However, in this version, we convert the TRUE and FALSE values returned by EXACT to 1s and 0s with the double negative :
--EXACT(data[Color],F5) // convert to 1s and 0s
This yields an array like this:
{0;0;0;0;1;0;0;0;1;0;0;0}
The positions of the 1s in this array correspond to the rows where the color is “RED”. This array is returned directly to the XLOOKUP function as the lookup array argument. We can now rewrite the formula like this:
=XLOOKUP(1,{0;0;0;0;1;0;0;0;1;0;0;0},data[Qty])
With a lookup value of 1, XLOOKUP finds the 1 in the 5th position and again returns the value at row 5 in the Quantity column: 10.
This use of 1s and 0s is common in formulas that use Boolean logic because Excel automatically coerces TRUE and FALSE values to 1s and 0s during any math operation. See below for an example.
Extending the logic
The structure of the logic can be easily extended. For example, to narrow the match to “RED” in May, you can use a formula like this:
=XLOOKUP(1,EXACT(data[Color],F5)*(MONTH(data[Date])=5),data[Qty])
Here, because each of the two expressions returns an array of TRUE FALSE values, and because these arrays are multiplied together, the math operation coerces the TRUE and FALSE values to 1s and 0s. It is not necessary to use the double-negative. The lookup value remains 1, as in the formula above. Once this operation is complete, the formula looks like this:
=XLOOKUP(1,{0;0;0;0;0;0;0;0;1;0;0;0},data[Qty])
XLOOKUP locates the 1 in the 9th position and returns 14 as a final result
First and last match
If there is more than one match in lookup_array , XLOOKUP will return the first match by default. To force XLOOKUP to return the last match, set the search mode argument for XLOOKUP to -1:
=XLOOKUP(1,--EXACT(data[Color],F5),data[Qty],,,-1) // last match
This version of the formula will return 14 as a final result.
If you need to return multiple results for multiple matches, see the FILTER function .
