There is a way to significantly speed this up (factor 3,000, see explanation below): If you sort the data in column C in worksheet
DNC Worksheet, you can run the
MATCH without the
0 at the end, i.e.
=MATCH(A2,'DNC Worksheet'!$C:$C). (Side note: Excel 2007 and higher is pretty smart when it comes to using the full column, so no need to specify the 100000 here!).
The initial downside of this approach is that you'll also get a match, even if A2 is not included in column C. However, this can be dealt with using the following formula:
=IF(INDEX('DNC Worksheet'!$C:$C,MATCH(A2,'DNC Worksheet'!$C:$C))=A2,MATCH(A2,'DNC Worksheet'!$C:$C),"no match")
Of course, you could half the calculation time of this, by having the match in one cell (say B2)- and then use this in the next cell
=IF(INDEX('DNC Worksheet'!$C:$C,B2)=B2,B2,"no match").
If you provide
VLOOKUP) with the
0/FALSE parameter at the end, Excel performs an exact search, i.e. it starts with the first cell in C2, checks if it matches. If not, it continues to the next until it either finds the value - or produces an
N/A! error. Therefore, if you have 100,000 dataset, it will take on average 50,000 comparison until the value is found (=n/2) - so in your case the total number of calculations is 1.25B!
FALSE parameter is omitted, Excel assumes that the range is sorted and applies a binary search algorithm: It starts in the middle of the range, i.e. cell 50,000 and checks if its value is larger or smaller. Assuming it's larger the value you're trying to match must therefore be between cell 1 and 50,000. Now it again checks in the middle of this range, etc. Therefore, it will take log2(n) comparison, in your case ~17. So the total number of calculations is a mere 425K, i.e. it'll calculate 3,000x faster! :-)
For further reading/performance tweaks, I recommend this website!