On Dec 7, 8:57 am, John Bundy (remove) wrote:
Test1a: pull each column into 2 seperate arrays, loop through 1 until the
answer is found then stop and go to the next. Time: 6.816 sec.
[....]
Test1c: insert a vlookup formula directly into the cell. Time: 6.093 sec.
[....]
The second series pitted the best two methods (formula directly in the cell,
loop array against another array) against each other at 65536 cells.

Test2a: looping through one array to find a match method. Time: 301.855
Test2b: formula directly in cell. Time: 73.320

Conclusion: I had predicted from other posts on the matter that formula in
cell would be faster, but i didn't think the difference would be that large.

This might largely be due to the time it takes to copy data between
Excel ranges and VBA arrays. If that's the case, the impact would
depend on the relative cost of the copying to the other processing in
the algorithm. At least, that is what I found in my experiments.