two dimensional arrays

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=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
 
This will return the address.

Assume the 4x4 grid is in the range A1:D4.

Try this array formula** :

=ADDRESS(MAX((A1:D4="five")*ROW(A1:D4)),MAX((A1:D4="five")*COLUMN(A1:D4)),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)
 
Thanks to both of you. Perfect.

T. Valko said:
This will return the address.

Assume the 4x4 grid is in the range A1:D4.

Try this array formula** :

=ADDRESS(MAX((A1:D4="five")*ROW(A1:D4)),MAX((A1:D4="five")*COLUMN(A1:D4)),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)
Click to expand...
 
Back
Top