Increase speed for match formula on over 130k rows

I have a dataset which is a little over 130k rows. Right now I run a match on the dataset against a master file (Column A) to return a true/false. All of this is in the same workbook.

Please Login or Register  to view this content.

If I run this formula on all recordd it takes about 20 minutes to run so I have been splittling the dataset up into smaller more manageble pieces.

Just found out today I need to add another match and compare 2 columns (A & B) to return true/false.

Please Login or Register  to view this content.

Is there some way to speed this process up so I can run it on all 130k at a time?

Also, been trying but can't seem to figure it out, but is there a way to incorporate the method were you search for where the data starts, then find the length and use all this information in an Offset construct.

So, instead of searching all 130k records you narrow the search and now only query against maybe 10k. Also, with Offset being volatile will this just add to the processing time.

Sample attached, but just contains a small amount of data.

Sort the data by col A then col B, and remove the ,0 from the match function. That results in a binary search instead of a linear search, which is exponentially faster

Ah. When you catenate A and B, the resulting strings are not in alphabetical order.

This worked:

=INDEX( TEXT(Data!$A$2:$A$30, "00") & Data!$B$2:$B$30, MATCH(TEXT(A2, "00") &B2, TEXT(Data!$A$2:$A$30, "00") & Data!$B$2:$B$30)) = TEXT(A2, "00") & B2

I would not use full-column references; instead, use a dynamic named range.

If you add a column on sheet Data and do the catenation there (=A2 & B2) , then sort ascending by that column, you get, in part,

Please Login or Register  to view this content.

Then you can revert the formula to,

=INDEX( Data!$C$2:$C$30, MATCH(A2 & B2, Data!$C$2:$C$30) ) = A2 & B2

Which returns, in part,

Please Login or Register  to view this content.

Would you compare the performance of that to this?

=LOOKUP(A2 & B2, Data!$C$2:$C$30) = A2 & B2

I was receiving some incorrect results,

Can you post a minimal example that illustrates?

With this new lookup formula will that negate the need to sort?

No! A binary search REQUIRES sorted data.

Excel help says the lookup_vector must be placed in ascending order so I would assume this would refer to C2:C30

Correct.

Thanks for posting results. Given that they are negligibly different, I'd opt for the shorter.