lookup cell address rather than its value

[ajlauten]

Hello,

I am trying to find the cell location/address of a value in a large array. The formula below returns the value in that cell (40) but I would like to return its address ($E$3). How would I go about doing that?

=HLOOKUP(WaterFalls!C4,'Calcs&fixed data for plots'!D2:BE23,2,FALSE)

 

[Fluff]

How about

=ADDRESS(2,MATCH(C4,'Calcs&fixed data for plots'!D2:BE2,0))

 

[ajlauten]

How about

=ADDRESS(2,MATCH(C4,'Calcs&fixed data for plots'!D2:BE2,0))

this returned $B$2 instead of $E$3, I was able to get the row corrected by changing your formula to =ADDRESS(3,MATCH(C4,'Calcs&fixed data for plots'!D2:BE2,0)) but can seem to correct the column from B to E

 

[Fluff]

Oops, it should be

=ADDRESS(3,MATCH(C4,'Calcs&fixed data for plots'!A2:BE2,0))

 

[kvsrinivasamurthy]

Try

=CELL("address",OFFSET('Calcs&fixed data for plots'!$D$3,0,MATCH(WaterFalls!C4,'Calcs&fixed data for plots'!$D$2:$BE$2,0)-1))

This gives result in the form of

'[Book4 (2).xlsx]Calcs&fixed data for plots'!$G$3

If you want only $G$3

=IFERROR(RIGHT(CELL("address",OFFSET('Calcs&fixed data for plots'!$D$3,0,MATCH(WaterFalls!C4,'Calcs&fixed data for plots'!$D$2:$BE$2,0)-1)),4),"")

 

[Fluff]

Why use volatile functions when they totally unnecessary?