get 2nd occurence value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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!
 
Back
Top