Concatinating inside MATCH

[ChemistB]

Hi All,

I'm trying to write a MATCH function in which the beginning row of the array (Argument 2) is based on a variable number (i.e. a number I pull from another cell or get through another MATCH statement). It would look something like this;

=MATCH("rabbit","B"&8:B12,0)

OR

=MATCH("rabbit","B"&MATCH("frog", B1:B12,0):B12,0).

I can do that with INDIRECT[ =INDIRECT("A"&MATCH("Rabbit",$B$1:$B$12,0))]
but it doesn't seem to work with MATCH. Is there a way to set this up so I can have that first cell in the array vary?

Thanks in advance.

ChemistB

[NBVC]

You mean perhaps something like?

=MATCH("rabbit",INDEX(B1:B12,MATCH("frog", B1:B12,0)):B12,0)

[ChemistB]

Thanks NBVC! I think that will do the trick. I'm a little confused as to why it works since if I break it up;

Please Login or Register  to view this content.

has the subfunction

Please Login or Register  to view this content.

which works out to be "frog" and not B4 like I would expect it to be. Am I missing something or is this one of those "just the way Excel works" kinda things.

Thanks again.
ChemistB

[NBVC]

the Index() function can also return the reference to a cell (See Index help files).

When put inside another formula such as the one provided, it will return the cell reference not the value in the indexed cell.