Finding nth Match in a List in Excel

问题:

I have a table in Excel from A1:A5

a  
a  
b  
a  
b

I want to find the row of the nth occurrence of the value 'a' using Excel Functions and not VBA

• If I put in 1 into this function, I want to get 1.

• If I put in 2 into this function, I want to get 2.

• If I put in 3 into this function, I want to get 4

A solution that works is to add the following formula =COUNTIF($A5:A5,"a") in B1 and then drag it down to B5.

I can then use =MATCH(C1,B1:B5,0) where C1 holds the variable n.

However is there a neater way without having to add extra columns?

回答1:

=AGGREGATE(15,6,1/(myMatch=myRange)*ROW(myRange),Occurrence)

The 1/(..) portion of the formula converts non-matches to DIV/0! errors, which the AGGREGATE function can ignore. So the Smallest value will be the first matching value; etc.

回答2:

this should work ARRAY FORMULA: CTRL + SHIFT + ENTER

=SMALL(IF(C1=$A$1:$A$5,ROW($A$1:$A$5)),$C$2)