Cell Address

[MrExceller]

Hi,

I have a range where I want to find a cell address based on another function, like HLOOKUP or any other. But when I use HLOOKUP function as a 'refernce' argument in Excel's inbuilt function Cell(), it gives an error.

Is there a way, that I can find a cell address based on any of the lookup functions.

Any help or I am asking too much from Excel

Thanks,

MrExceller

 

[somnath_it2006]

Check the ADDRESS function on this page - http://www.techonthenet.com/excel/formulas/address.php

Use MATCH function for get the row number and column number... (Row number And Column number is the parameter for ADDRESS function)

 

[MrExceller]

=SUM(OFFSET(INDIRECT(ADDRESS(ROWS(MATCH(Q$1,$1:$1,0))+ROW()-1,MATCH(Q$1,$1:$1,0),4,1)),,-12):OFFSET(INDIRECT(ADDRESS(ROWS(MATCH(Q$1,$1:$1,0))+ROW()-1,MATCH(Q$1,$1:$1,0),4,1)),,-1,,))


I think I got it - the logic behind the formulae above is to look for a value in Q1, and identify the dynamic cell address by the help of INDIRECT, ADDRESS, ROWS function and creates dynamic range of 12 columns before column Q through OFFSET function.

If anybody knows simpler way of doing it, I would be glad to use

Thanks,

Thanks also somnath_it2006