Formula to return ADDRESS of cell in range that meets criteria

Using Excel 2000
I want a formula to find a cell containing a specified value and then return
the cell address for that cell so that I can use that address to define a
range for another formula. Hlookup provides the value. Match provides the
position. How can I find the address without already knowing the address?

Thank You,

Hi!

As long as there are no duplicate values in your lookup
table this will work. You could put this all together as a
single formula but for the sake of simplicity, keep it as
separate formulas:

Assume the value returned by your lookup formula is in
cell A2. The range of your lookup table is D2:I6. E2:I6
are column headers and D3:D6 are row headers.

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=ADDRESS(MAX((D2:I6=A2)*ROW(D2:I6)),MAX((D2:I6=A2)*COLUMN
(D2:I6)))

Now, assume this formula is in cell A3 and returns the
value $F$6. To use $F$6 as a reference in another formula
you'd have to use INDIRECT:

=SUM(INDIRECT(A3&":F20"))

Biff

>-----Original Message-----
>Using Excel 2000
>I want a formula to find a cell containing a specified
value and then return
>the cell address for that cell so that I can use that
address to define a
>range for another formula. Hlookup provides the value.
Match provides the
>position. How can I find the address without already
knowing the address?
>
>Thank You,
>.
>