two dimensional arrays

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave Breitenbach - 19 Sep 2007 18:24 GMT

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

Reply to this Message

Peo Sjoblom - 19 Sep 2007 18:35 GMT

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

entered with ctrl + shift & enter

where MyGrid is your 4X4 table

Signature

Regards,

Peo Sjoblom

> Is there an Excel function that can point at a two-dimensional array and
> return the location of a targeted text string?
[quoted text clipped - 8 lines]
> tia,
> Dave

Reply to this Message

T. Valko - 19 Sep 2007 18:37 GMT

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)

Signature

Biff
Microsoft Excel MVP

> Is there an Excel function that can point at a two-dimensional array and
> return the location of a targeted text string?
[quoted text clipped - 8 lines]
> tia,
> Dave

Reply to this Message

Dave Breitenbach - 19 Sep 2007 20:24 GMT

Thanks to both of you. Perfect.

> This will return the address.
>
[quoted text clipped - 21 lines]
> > tia,
> > Dave

Reply to this Message

T. Valko - 19 Sep 2007 21:12 GMT

You're welcome!

Signature

Biff
Microsoft Excel MVP

> Thanks to both of you. Perfect.
>
[quoted text clipped - 26 lines]
>> > tia,
>> > Dave

Reply to this Message