Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locate values in range | Excel Discussion (Misc queries) | |||
locate a cell value within a range and return its reference | Excel Discussion (Misc queries) | |||
Need a formula to locate the blank cells within the range | Excel Discussion (Misc queries) | |||
Locate month n year from range of date | New Users to Excel | |||
How can I locate the second to last value in a range? | Excel Worksheet Functions |