get 2nd occurence value

T

TUNGANA KURMA RAJU

Match function fetches row number of 1st occurence of a value in a range,How
to get 2nd or 3rd or later occurences of that value in a range?
 
D

Domenic

Assuming that A1:A100 contains your data, and that B1 contains your
lookup value, try the following for the second occurrence...

=SMALL(IF(A1:A100=B1,ROW(A1:A100)-ROW(A1)+1),2)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. For the third
occurrence, change the 2 at the end of the formula to 3.

Hope this helps!
 
T

TUNGANA KURMA RAJU

Hello DOMENIC,
Thanks.you have done it.It helped me to solve a problem.With regards
 
Top