Cell address

T

Tasha

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
 
P

Peo Sjoblom

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

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

Regards,

Peo Sjoblom


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

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
Click to expand...
(a1,range,1,false),1,1). However I want the lookup
function to return the cell address instead of the value.
Any suggestions?
 
Top