Return the column number of the nth occurrence in a row

[gruebz]

Hi, I couldn't find this answered exactly on the forum. Sorry if it has.

I am looking to return the column number of the nth occurrence in a row.

So in the example in the table below, I am looking to return the column number in row 1 of the 2nd (B4) occurrence of the word 'dog' (B3). In this case it would return the number 6 as the 2nd instance of dog is in column F.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]cat[/TD]
[TD]dog[/TD]
[TD]cat[/TD]
[TD]rat[/TD]
[TD]mouse[/TD]
[TD]dog[/TD]
[TD]dog[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]lookup[/TD]
[TD]dog[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]nth[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance

 

[Marcelo Branco]

Welcome to Mr Excel forum

EDIT
Try this array formula
=SMALL(IF(A1:G1=B3,COLUMN(A1:G1)-COLUMN(A1)+1),B4)
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[gruebz]

Thank you so much. Exactly what I needed