Excel 2007 : performance issue with a lookup function

Posted a problem last week that Marcol solved with results exceeding expectations.. My problem now is that I gave him test data that was 1% of the real data I'm sorting through.

When I use the lookup function he created:

=LOOKUP(REPT("Z",255),CHOOSE({1;2},"",INDEX($A$2:$A$21871,MATCH(TRUE,INDEX(ISNA(MATCH($A$2:$A$21871,$B$1:$B313,0)),0),0))))

and I apply it to the 21,871 lines I'm actually working with it takes 30 minutes for it to finish... Are there any alternatives (code wise) to this that would speed it up or are there other measures (hardware) I can take to speed this up?

thanks,
Bud

Try using a helper column with formula:

=ISNA(MATCH($A$2, $B$1:$B313,0))

copied down.

then

=IFERROR(INDEX($A$2:$A$21871,MATCH(TRUE,$C$2:$C$21871,0)),"")

where C2:C2187 is the helper column range....

In your original formula it seems that you are looking in column A and finding when a value in column A is not in the list identified in B1:B313. The first time it finds that a value in column A is not in B1:B13, then the INDEX returns that item from column A that did not match.

Well, put this in a free column at row 2, like, say C2: =ISNA(MATCH($A$2, $B$1:$B313,0))

and copied down will check each cell in column A individually to see if there is a match in B1:B313.... you should get TRUE if there is not a match in B1:B13 and FALSE if there is a match.

The second formula: =IFERROR(INDEX($A$2:$A$21871,MATCH(TRUE,$C$2:$C$21871,0)),"") is a single cell formula that should give same result as the original formula. It assumes you put the ISNA(MATCH()) formula in column C and copied down.

If this is not giving the intended results, then you need to describe the problem/requirement from scratch.