Finding 2nd or 3rd or nth Match

[riwiseuse]

The MATCH command (Type 0) returns the location in the lookup range of the first exact match.

Suppose I want the 2nd or 3rd exact match? is there such a thing as MATCH2 or MATCH2nd, or what workarround can you recommend.

Thanks,

Brian

[NBVC]

Without a helper column you can use an array formula:

If your data is in A1:A10, then:

=SMALL(IF(A1:A10="X",ROW(A1:A10)-ROW(A1)+1),2)

confirmed with CTRL+SHIFT+ENTER not just ENTER..

where "X" is the search value.. and the final 2 is the 2nd match... change to nth match needed.

With a helper column, in adjacent column:

=COUNTIF(A$1:A1,"X")

copied down

then use formula

=MATCH(2,B1:B10,0)

again where "X" is search value and 2 is nth match desired.

[riwiseuse]

Without a helper column you can use an array formula:

If your data is in A1:A10, then:

=SMALL(IF(A1:A10="X",ROW(A1:A10)-ROW(A1)+1),2)

confirmed with CTRL+SHIFT+ENTER not just ENTER..

where "X" is the search value.. and the final 2 is the 2nd match... change to nth match needed.

NBVC,

Thanks for your help. I always appreciate the attention that you and others pay to these posts and don't want my ADD (thats the Attention Deficit Function not the SUM) approach to problem solving to lead anyone to think that I don't follow up and digest the suggestions that forum members are good enough to offer.

I tend to keep a lot of things in the hopper, but I am always serious about solving the problems.

I'm focusing on your single cell solution because I actually have a moving criteria over a range of 24000 records, so I would need to add 24000 rows to maintain the extra column data for each criteria.

I am not familiar with the SMALL function. Does it return the lesser of the values separated by commas. Or maybe the IF function on the array returns multiple true and flase readings and then I get the second smallest back . . . from that function.

The IF function inside the SMALL seems to look for matchs of the criteria in the array range specified.

you wrote sample:

IF(A1:A10="X",ROW(A1:A10)-ROW(A1)+1)

I'm unclear on the conditional test with this syntax. Does the test A1:A10="X" return a number within the IF function as to how many values in the array ="X" or do they all have to ="X" in order to get the true result.

And there doesn't appear to be a false result, so I guess this assumes there is a match which wouldn't be true in every case - another wrench.

As to the true value, does ROW(A1:A10) return the number of row in the array?

So, although I don't understand quite what is set out to be accomplished, my operational version would being something like this

IF ($Q$2:$Q$24481=M2,. . .

I'm not clear on how the Row comparison in the true value of the IF returns the row of the second match, or the 3rd Match if I change the 2 to a 3, etc. but if it does, I'd be all set.

Thanks,

Brian