two dimensional arrays

[Dave Breitenbach]

Is there an Excel function that can point at a two-dimensional array and
return the location of a targeted text string?

For example, if there is a 4X4 grid of cells which have text in them, I'd
want the formula to return the column and row number within the grid where it
finds the word "five."

Any ideas? I'd rather not use VBA, but I'll listen to anything.

tia,
Dave

 

[Peo Sjoblom]

=ADDRESS(MAX((MyGrid="Five")*(ROW(MyGrid))),MAX((MyGrid="Five")*(COLUMN(MyGrid))))


entered with ctrl + shift & enter

where MyGrid is your 4X4 table



--


Regards,


Peo Sjoblom

 

[T. Valko]

This will return the address.

Assume the 4x4 grid is in the range A14.

Try this array formula** :

=ADDRESS(MAX((A14="five")*ROW(A14)),MAX((A14="five")*COLUMN(A14)),4)

This assumes that there is just a single instance of "five".

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Dave Breitenbach]

Thanks to both of you. Perfect.

This will return the address.

Assume the 4x4 grid is in the range A14.

Try this array formula** :

=ADDRESS(MAX((A14="five")*ROW(A14)),MAX((A14="five")*COLUMN(A14)),4)

This assumes that there is just a single instance of "five".

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks to both of you. Perfect.