Locate value in a range
 

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!

Locate value in a range
 

This array formula will return the row

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

--
__________________________________
HTH

Bob

"Matt" wrote in message
...
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!

Locate value in a range
 

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

--
Gary''s Student - gsnu200824


"Matt" wrote:

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!

Locate value in a range
 

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)*COLUM N(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.

--
Biff
Microsoft Excel MVP


"Gary''s Student" wrote in message
...
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

--
Gary''s Student - gsnu200824


"Matt" wrote:

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!

Locate value in a range
 

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,"")))


--
__________________________________
HTH

Bob

"Gary''s Student" wrote in message
...
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

--
Gary''s Student - gsnu200824


"Matt" wrote:

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!