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.

	A	B	C	D	E	F	G
1	cat	dog	cat	rat	mouse	dog	dog
2
3	lookup	dog
4	nth	2

<tbody>
</tbody>

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