Explanation

In the example shown, cell G4 contains the lookup value, “Berlin”. XLOOKUP is configured to find this value in the table, and return the population. The formula in G5 is:

=XLOOKUP(G4,B5:B18,D5:D18) // get population
  • The lookup_value comes from cell G4
  • The lookup_array is the range B5:B18, which contains City names
  • The return_array is D5:D18, which contains Population
  • The match_mode is not provided and defaults to 0 (exact match)
  • The search_mode is not provided and defaults to 1 (first to last)

To return County instead of population, only the return array is changed. The formula in G6 is:

=XLOOKUP(G4,B5:B18,C5:C18) // get country

XLOOKUP vs VLOOKUP

The equivalent VLOOKUP formula to retrieve population is:

=VLOOKUP(G4,B5:D18,3,0)

There are a few notable differences which make XLOOKUP more flexible and predictable:

  • VLOOKUP requires the full table array as the second argument. XLOOKUP requires only the range with lookup values.
  • VLOOKUP requires a column index argument to specify a result column. XLOOKUP requires a range.
  • VLOOKUP performs an approximate match by default . This behavior can cause serious problems . XLOOKUP performs an exact match by default.

Dynamic Array Formulas are available in Office 365 only.

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

  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.