This formula works but I donīt understand why Index(Index) match

Hi,

Let's say I have a column (A1:A10) where I have several animals, and I want to find the position of the second "Cat" which I write in cell B1.

Col A
Cat
Dog
Bear
Cat
Turtle

I can use this formula (it works) =MATCH(B1,INDEX(INDEX(A1:A10,MATCH(B1,A1:A10,0)+1):A10,0),0)+MATCH(B1,A1:A10,0), my issue is with the bold part. (INDEX(A1:A10,MATCH(B1,A1:A10,0)+1), this will give you "Dog" than you have an Index ("dog":A10,0)..... how can this work? I get the general idea that relative to dog the 2nd Cat is in the position 3, and then you sum the first to get the position from the beginning.

Thanks

Regarding this formula:

Please Login or Register  to view this content.

1) When you resolve the inner INDEX section, Excel shows you the contents of the cell. But, internally, the INDEX function returns a reference. In your case, it's a reference to cell A2.
2) Because you're constructing an array for the outside MATCH function to reference, you'd need to either CTRL+SHIFT+ENTER the formula or wrap the array in an INDEX function (eliminating the need to C+S+E).

Side note: Either of these two functions will return the row reference you want
This ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER)

Please Login or Register  to view this content.

or this regular formula

Please Login or Register  to view this content.

Does that help?