Locate value in a range

[Matt]

I'm trying to locate a value in a range that is 12 x 30. Since its
not just a row or column I can't use match. Is there a way to use
match in "2D"?

Thanks!

 

[Bob Phillips]

This array formula will return the row

=MIN(IF(A1:L30="xxx",ROW(A1:L30)-MIN(ROW(A1:L30))+1,""))

 

[Gary''s Student]

We can get the full 2D address using Bob's formula. In Bob's example the
array was A1 thru L30. Say "XXX" is in cell C13. In cell A31, enter Bob's
array formula:

=MIN(IF(A1:L30="xxx",ROW(A1:L30)-MIN(ROW(A1:L30))+1,"")) this displays 13

In some other cell, enter:

=MATCH("XXX",INDIRECT(A31 & ":" & A31)) this displays $C$13

 

[T. Valko]

Not a lot of detail to go with...

Array entered** :

Assuming A1 is the lookup_value and is not part of "rng".

Also assumes that there is only one instance of the lookup_value in rng.

=ADDRESS(MAX((rng=A1)*ROW(rng)),MAX((rng=A1)*COLUMN(rng)),4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Bob Phillips]

You could also use

=ADDRESS(=MIN(IF(A1:L30="xxx",ROW(A1:L30)-MIN(ROW(A1:L30))+1,"")),
=MIN(IF(A1:L30="xxx",COLUMN(A1:L30)-MIN(COLUMN(A1:L30))+1,"")))