I came up with the following worksheet formula, beacuse as I cycle through
each row on one sheet, I need to find the corresponding record on the other
sheet to pull some additional data. There are two identifiers that must both
be used to find the unique matching row.

{=MATCH(D8&J8,('Raw'!A1:A65000)&('Raw'!C1:C65000), FALSE)}

The problem, of course, is that due to the size of the array from the second
sheet, each match takes way too long (in excess of 5 seconds per row, with
an average of 8000-10000 rows that I need to process). The second sheet will
often be full or close to full, which is why I'm going to 65K rows.

Is a find loop (like below) where I reset the range after every false match
the fastest option, or is there a better way to do this in VBA? This seems
overly complicated, especially since I'm not sure how many false matches
(non-matching values) there would be (it can vary) so I'm not sure how to
determine how many loops I should use. (or maybe with the exit for, it
doesn't matter?)

I'm currently looking at something like the aircode below, but it just
doesn't seem elegant. For example, if a match of the initial number isn't
found at all (which is possible) then it would crash as soon as the match
function returned an error (on the line that tries to increment the
RawRangeStart to NA()+1). If there is a better way than my frantic looping,
please let me know.
Thanks,
Keith
XP/XL2003

For i = 1 to 8000
RawRangeStart = 1
for multiplevalues = 1 to 10 'or 20? I don't know how many false
matches I need to prepare for
set rangeA = Sheet(Raw).range("A" & Cstr(RawRangeStart) & ":A65000")
'resize the range
x = Application.match(valuefromD, rangeA,False) 'find the match in the
remainder of the range
if valuefromJ=valuefromC then
'do my calculations
exit for 'I think this drops just to the most recent for,
the multiplevalues and not the i loop?
else
RawRangeStart = x + 1
end if
Next multiplevalues
Next i