How do I get the cell address of a VLOOKUP reference?
 

I'm trying to get the cell address, not the contents, of a cell referenced
using VLOOKUP. I've tried '=CELL("address", VLOOKUP(5,Table,2,false)', but
I'm getting a formula error. EXCEL 2003. Thanks.

How do I get the cell address of a VLOOKUP reference?
 

One way:

=CELL("address",OFFSET(Table,MATCH(5,OFFSET(Table, ,,,1),FALSE)-1,1))

If you define Table to just be the first column of your table:

=CELL("address",OFFSET(Table,MATCH(5,Table,FALSE)-1,1))


VLOOKUP returns a value, not a range reference.

In article ,
"tfleischny" wrote:

I'm trying to get the cell address, not the contents, of a cell referenced
using VLOOKUP. I've tried '=CELL("address", VLOOKUP(5,Table,2,false)', but
I'm getting a formula error. EXCEL 2003. Thanks.

How do I get the cell address of a VLOOKUP reference?
 

Another way

=CELL("address",INDEX(Table,MATCH(7.5,INDEX(Table, ,1),0),2))


--

Regards,

Peo Sjoblom

"JE McGimpsey" wrote in message
...
One way:

=CELL("address",OFFSET(Table,MATCH(5,OFFSET(Table, ,,,1),FALSE)-1,1))

If you define Table to just be the first column of your table:

=CELL("address",OFFSET(Table,MATCH(5,Table,FALSE)-1,1))


VLOOKUP returns a value, not a range reference.

In article ,
"tfleischny" wrote:

I'm trying to get the cell address, not the contents, of a cell
referenced
using VLOOKUP. I've tried '=CELL("address", VLOOKUP(5,Table,2,false)',
but
I'm getting a formula error. EXCEL 2003. Thanks.

How do I get the cell address of a VLOOKUP reference?
 

Oops! 7.5 should of course be 5

--

Regards,

Peo Sjoblom

"Peo Sjoblom" wrote in message
...
Another way

=CELL("address",INDEX(Table,MATCH(7.5,INDEX(Table, ,1),0),2))


--

Regards,

Peo Sjoblom

"JE McGimpsey" wrote in message
...
One way:


=CELL("address",OFFSET(Table,MATCH(5,OFFSET(Table, ,,,1),FALSE)-1,1))

If you define Table to just be the first column of your table:

=CELL("address",OFFSET(Table,MATCH(5,Table,FALSE)-1,1))


VLOOKUP returns a value, not a range reference.

In article ,
"tfleischny" wrote:

I'm trying to get the cell address, not the contents, of a cell
referenced
using VLOOKUP. I've tried '=CELL("address",
VLOOKUP(5,Table,2,false)',
but
I'm getting a formula error. EXCEL 2003. Thanks.

How do I get the cell address of a VLOOKUP reference?
 

In article ,
"Peo Sjoblom" wrote:

Another way

=CELL("address",INDEX(Table,MATCH(7.5,INDEX(Table, ,1),0),2))

Well, OK, if you want to be *efficient* about it...<g

Had OFFSET stuck in my head, obviously...Thanks for the correction!