Nth Position of Occurrence in a List

[Ansel]

I am trying to write a formula in one cell that will give
me the Position of the Nth occurrence of an Item in a
List. I know I can get that by creating helper column of
formulas next to the List, but I want to avoid that.

I know I can get the position of the first occurrence with
the MATCH function. And I can get the position of the
second occurrence with the formula:

=MATCH(Item,List,0)+MATCH(Item,INDEX(List,MATCH
(Item,List,0)+1,0):INDEX(List,ROWS(List)),0)

but this approach becomes impractical after the second.

Any help would be appreciated.

Thanks.

 

[Alan Beban]

I am trying to write a formula in one cell that will give
me the Position of the Nth occurrence of an Item in a
List. I know I can get that by creating helper column of
formulas next to the List, but I want to avoid that.

I know I can get the position of the first occurrence with
the MATCH function. And I can get the position of the
second occurrence with the formula:

=MATCH(Item,List,0)+MATCH(Item,INDEX(List,MATCH
(Item,List,0)+1,0):INDEX(List,ROWS(List)),0)

but this approach becomes impractical after the second.

Any help would be appreciated.

Thanks.

If you want to make the functions in the freely downloadable file at
http://home.pacbell.net/beban available to your workbook, you can use

=INDEX(ArrayMatch(Item,List,N)) to return the index number of the item
within the list.

Alan Beban

 

[Harlan Grove]

I am trying to write a formula in one cell that will give
me the Position of the Nth occurrence of an Item in a
List. I know I can get that by creating helper column of
formulas next to the List, but I want to avoid that.

I know I can get the position of the first occurrence with
the MATCH function. And I can get the position of the
second occurrence with the formula:

=MATCH(Item,List,0)+MATCH(Item,INDEX(List,MATCH
(Item,List,0)+1,0):INDEX(List,ROWS(List)),0)

but this approach becomes impractical after the second.

To find the row index of the Nth match for X in List, try the array formula

=SMALL(IF(List=X,ROW(List)),N)-CELL("Row",List)+1