How can I match data fields in two columns on one file with those in another file?

O2:

Formula:

Please Login or Register  to view this content.

Array Entered with Ctrl-Shift-Enter rather than just Enter.


Regards, TMS

First thought is to add a helper column with just the MATCH part of the formula ... that's the Array part. Then you could have the INDEX formula part use the helper column. That would halve the Array formula calculations. Even better might be to use VBA to drop the MATCH part of the formula into the helper column, say 1000 cells at a time and convert the formulae to values. Then you would have anything like the ongoing calculation. The downside, of course, is that the indexes become static.

Try the helper column first and see if that helps. Bear in mind that the MATCH is still an Array Formula BUT the INDEXes are not.

Regards, TMS

Have you tried setting calculation to manual and then copying and pasting the values?

My bad... At first, I thought it was working fine, but figured it was taking way too long.

Yes, the sample file that I provided contained only about 7000 rows to search, compare, and match. Like I said, I have more than 500,000 rows in both "Atmos" and "Surface" meaning that it has to run 500,0000^2 times... I figured this when I filtered them, I could only see "Dry" as an available option meaning that Excel is still calculating.. At the bottom where it gives status, it is saying "Calculating: (4 Processors: 0%) for about 20 minutes now and my computer is being overheated... Will there be a (more) efficient way to do this (VBA)???
Thanks!