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.
The result is a much faster formula.
The reason XLOOKUP is slow in this mode is that there isno requirementthat the lookup values be sorted.
This is sometimes referred to as alinear search.
If values are sorted inascending order, use the value 2 to enable binary search.
With binary search enabled, XLOOKUP will run very fast.
For a complete XLOOKUP overview with many examples, seeHow to use XLOOKUP.
If we don’t find the invoice number, we don’t want to show anything.
To require an exact match, we use 0 formatch_mode.
To enable XLOOKUP’S binary search mode, we use 2 forsearch_mode.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
you’re free to use INDEX to retrieve individual values, or entire rows and columns.
It is a more robust and flexible successor to the MATCH function.
XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?)
…
