XLookup binary search failure

Mr_Phil

Board Regular
Joined
May 28, 2018
Messages
147
Office Version
  1. 365
Hi. I have a large set of data to search thru and was reading about the differences between the various lookups. I’m trying to speed things up and I read about using a binary search with xlookup so I made a copy of the workbook (all ten megabytes) and sandboxed it so it was alone. That way I couldn’t accidentally influence the actual currently working file.

The formula looks like this.
Excel Formula:
=XLOOKUP(TechName&Item, OthersheetTechName&OthersheetItem, OthersheetMin,,,2)
I typed that on my ipad so I might have too many commas before the 2 indicating a binary search. “Othersheet” is a four column range on a different sheet that is Name, Item, Min, Max.

By Concatenating the name and item from the issue sheet and the columns from the “other” sheet I get a successful result from the value range specified. It’s just slow. I thought that was because there are about 40K rows hence the search to find a faster option. I sorted the other sheet ascending by name then part. But, adding that 2 caused an N/A error.

When I get back to the computer I will try to replicate it on a smaller scale and share it on my google drive. Right now the working file uses an Index and Match setup because it is two seconds faster than the concatentated xlookup. But since the binary lookup allegedly was faster still I just had to give it a try. I now have three copies. One is the working with Index Match, then the concatentated double xlookup, and now the failed binary lookup. But, I had to get the question out there because I will obsess over it if I don’t.

Thank you for any troubleshooting ideas or insights.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top