match nth occurence

[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

 

[Joel]

Are you using VBA or formulas?

 

[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)))

 

[Ron Rosenfeld]

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

Take a look at the Arbitrary Lookups section on this page:

http://www.cpearson.com/excel/TablesAndLookups.aspx

--ron