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,
.