find position of 2nd, 3rd, 4th occurence of word in list

[jammoca]

If I have a list of words in columnA, I already have a formula that finds the position of the first occurence of the word "hello" which is found in cell B2 :

=MATCH(B2,$A:$A,0)

But is there a formula that will find the 2nd occurence of the same word, which I can then manipulate to find the 3rd occurence, 4th occurence etc etc etc.

 

[c_m]

see:
http://www.mrexcel.com/forum/showthread.php?t=342205

 

[Aladin Akyurek]

If I have a list of words in columnA, I already have a formula that finds the position of the first occurence of the word "hello" which is found in cell B2 :

=MATCH(B2,$A:$A,0)

But is there a formula that will find the 2nd occurence of the same word, which I can then manipulate to find the 3rd occurence, 4th occurence etc etc etc.

E2 houses a value from 1,2,3,...,N.

Control+shift+enter, not just enter...

=SMALL(IF($A$2:$A$1000=B2,ROW($A$2:$A$1000)),E2)

would yield the row of the Nth occurrence of the item in B2 as specified in E2, and the following the address:

=CELL("address",INDEX($A$2:$A$1000,SMALL(IF($A$2:$A$1000=B2,ROW($A$2:$A$1000)),E2)))