+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    tfleischny
    Guest

    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.

  2. #2
    JE McGimpsey
    Guest

    Re: 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 <C838DD07-2AFC-491A-852D-44A45489B95B@microsoft.com>,
    "tfleischny" <tfleischny@discussions.microsoft.com> 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.


  3. #3
    Peo Sjoblom
    Guest

    Re: 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" <jemcgimpsey@mvps.org> wrote in message
    news:jemcgimpsey-89C035.15490604012006@msnews.microsoft.com...
    > 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 <C838DD07-2AFC-491A-852D-44A45489B95B@microsoft.com>,
    > "tfleischny" <tfleischny@discussions.microsoft.com> 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.




  4. #4
    Peo Sjoblom
    Guest

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

    Oops! 7.5 should of course be 5

    --

    Regards,

    Peo Sjoblom

    "Peo Sjoblom" <terre08@mvps.org> wrote in message
    news:ehyFuNYEGHA.3856@TK2MSFTNGP12.phx.gbl...
    > Another way
    >
    > =CELL("address",INDEX(Table,MATCH(7.5,INDEX(Table,,1),0),2))
    >
    >
    > --
    >
    > Regards,
    >
    > Peo Sjoblom
    >
    > "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
    > news:jemcgimpsey-89C035.15490604012006@msnews.microsoft.com...
    > > 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 <C838DD07-2AFC-491A-852D-44A45489B95B@microsoft.com>,
    > > "tfleischny" <tfleischny@discussions.microsoft.com> 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.

    >
    >




  5. #5
    JE McGimpsey
    Guest

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

    In article <ehyFuNYEGHA.3856@TK2MSFTNGP12.phx.gbl>,
    "Peo Sjoblom" <terre08@mvps.org> 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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1