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.
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.
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 <[email protected]>,
"tfleischny" <[email protected]> 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.
Another way
=CELL("address",INDEX(Table,MATCH(7.5,INDEX(Table,,1),0),2))
--
Regards,
Peo Sjoblom
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> "tfleischny" <[email protected]> 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.
Oops! 7.5 should of course be 5
--
Regards,
Peo Sjoblom
"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> Another way
>
> =CELL("address",INDEX(Table,MATCH(7.5,INDEX(Table,,1),0),2))
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> "JE McGimpsey" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <[email protected]>,
> > "tfleischny" <[email protected]> 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.
>
>
In article <[email protected]>,
"Peo Sjoblom" <[email protected]> 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!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks