XLOOKUP binary search

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

1. This approach is overkill unless lookup performance is an issue.
2. VLOOKUP does not have a binary search mode, but there is a workaround.
3. See XLOOKUP vs INDEX and MATCH for a detailed comparison.