Formula to return ADDRESS of cell in range that meets criteria

[Guest]

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,

 

[Biff]

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 D36 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