return address of found value with vlookup

Discussion in 'Microsoft Excel Discussion' started by sybmathics, Jan 14, 2007.

  1. sybmathics

    sybmathics Guest

    Hi,

    I'm searching in a large table for a specified date with vlookup. the
    function returns a value form the 6th column in the lookup table, where the
    last argument is set to TRUE.

    My question now is: can excel also return the address of the cell with the
    value that the vlookup returns?

    Any help is greatly appreciated.

    greets,


    Sybolt
     
    sybmathics, Jan 14, 2007
    #1
    1. Advertisements

  2. sybmathics

    Bob Phillips Guest

    If you use MATCH on the first column instead of VLOOKUP, you will get the
    index of the matching item within the table

    =MATCH("some_val",M1:M100,0)



    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "sybmathics" <> wrote in message
    news:45aa0222$0$25996$4all.nl...
    > Hi,
    >
    > I'm searching in a large table for a specified date with vlookup. the
    > function returns a value form the 6th column in the lookup table, where

    the
    > last argument is set to TRUE.
    >
    > My question now is: can excel also return the address of the cell with the
    > value that the vlookup returns?
    >
    > Any help is greatly appreciated.
    >
    > greets,
    >
    >
    > Sybolt
    >
    >
    >
    >
    >
     
    Bob Phillips, Jan 14, 2007
    #2
    1. Advertisements

  3. sybmathics

    Guest Guest

    This will return the address.

    =ADDRESS(MATCH(DATE(2007,1,25),$A$4:$A$503,1)+3,6)

    You need to modify the +3 and the 6 at the end.

    the +3 is the starting row for the match -1 hereI start on row 4 (A4)
    therefore 4-1=3
    the 6 is the 6th column across like the vlookup.

    you may want to look at the last item in the match ,1) this is the match
    type you indicate that for vlookup you use TRUE and I believe that this
    corresponds to 1 in MATCH.

    --
    Hope this helps
    Martin Fishlock, Bangkok, Thailand
    Please do not forget to rate this reply.


    "sybmathics" wrote:

    > Hi,
    >
    > I'm searching in a large table for a specified date with vlookup. the
    > function returns a value form the 6th column in the lookup table, where the
    > last argument is set to TRUE.
    >
    > My question now is: can excel also return the address of the cell with the
    > value that the vlookup returns?
    >
    > Any help is greatly appreciated.
    >
    > greets,
    >
    >
    > Sybolt
    >
    >
    >
    >
    >
    >
     
    Guest, Jan 14, 2007
    #3
  4. sybmathics

    sybmathics Guest

    Working a bit with the match and indirect functions helped me solve the
    problem.

    Thanks a lot, the both of you.

    cheers,

    Sybolt
     
    sybmathics, Jan 14, 2007
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ivan

    use Vlookup for sum all found value?

    Ivan, Jul 5, 2004, in forum: Microsoft Excel Discussion
    Replies:
    2
    Views:
    319
  2. snewham
    Replies:
    1
    Views:
    355
  3. Ben
    Replies:
    4
    Views:
    27,489
    Gord Dibben
    Nov 15, 2010
  4. Jagaude

    vlookup return multiple value

    Jagaude, Feb 27, 2008, in forum: Microsoft Excel Discussion
    Replies:
    6
    Views:
    207
    Pete_UK
    Feb 29, 2008
  5. skiing

    vlookup function to return the cell address of the found item

    skiing, Jul 9, 2008, in forum: Microsoft Excel Discussion
    Replies:
    2
    Views:
    509
    Don Guillett
    Jul 9, 2008
Loading...

Share This Page