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.
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.
The formula looks like this.
Excel Formula:
=XLOOKUP(TechName&Item, OthersheetTechName&OthersheetItem, OthersheetMin,,,2)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.
