Hi all,
Happy Friday!
One of the useful features in XLOOKUP is "binary search." A binary search is optimized for speed. The idea is to repeatedly divide a lookup array in half until a target value is found. This "by halves" behavior means a binary search is very fast, but the
data must be sorted.
In the worksheet below, XLOOKUP is used to look up the amount for 1000 sequential invoice numbers in a table that contains 1 million invoices. The formula in cell F5 is:
=XLOOKUP(E5,data[Invoice],data[Amount],"",0,2)
[
Download the workbook and read the full explanation]
What about VLOOKUP? Can you configure a binary search with VLOOKUP? No, not directly. However, you can approximate a binary search with a formula like this:
=IF(VLOOKUP(A1,data,1)=A1,VLOOKUP(A1,data,n),NA())
Using VLOOKUP twice in a formula optimized for speed may seem like a bad idea, but there is a method to the madness.
Read the full explanation here.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
