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

#### jammoca

##### Banned
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.

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### Aladin Akyurek

##### MrExcel MVP
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)))

Replies
6
Views
271
Replies
8
Views
340
Replies
1
Views
1K
Replies
2
Views
263
Replies
6
Views
1K

Threads
1,190,798
Messages
5,982,976
Members
439,810
Latest member
phobo3s

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back