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.
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:
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