match nth occurence

M

Max Scott

Hi

I can use match to find the first occurence of a value in a list - how can i
find the 2nd, 3rd, nth value in a list? I'm using excel 2007 btw.

Thanks

Max
 
B

Bob Phillips

Try this array formula, searching for the value 2

=IF(ISERROR(SMALL(IF($A$2:$A$20=2,ROW($A$2:$A$20),""),ROW(A1))),"",SMALL(IF($A$2:$A$20=2,ROW($A$2:$A$20),""),ROW(A1)))
 
Top