Cell address

[Guest]

I want to do something like Offset(vlookup(a1,range,1,false),1,1). However I want the lookup function to return the cell address instead of the value. Any suggestions?

TIA

Tasha

 

[Peo Sjoblom]

=CELL("address",INDEX(range,match(a1,range,0)))

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

I want to do something like Offset(vlookup(a1,range,1,false),1,1). However

I want the lookup function to return the cell address instead of the value.
Any suggestions?

 

[Jason Morin]

It looks like you want to find the value of A1 in your
range and then return the address of the cell that is 1
column to the right and 1 row below it:

=ADDRESS(MAX(ISNUMBER(FIND(A1,rng))*ROW(rng))+1,MAX
(ISNUMBER(FIND(A1,rng))*COLUMN(rng))+1,4)

Array-entered, meaning press ctrl/shift/enter.

HTH
Jason
Atlanta, GA

-----Original Message-----
I want to do something like Offset(vlookup

(a1,range,1,false),1,1). However I want the lookup
function to return the cell address instead of the value.
Any suggestions?