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, . |
All times are GMT +1. The time now is 01:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com