Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reverse Lookup (2000)

    I am trying to figure out how to do a reverse lookup. I am using the following data for example. I am using columns C, D and E
    Client Managers CM. #
    Jones, Judy 14
    Dominici, Mark 02
    Henry, David 83
    Sproule, Bob 71
    Stine, Shirley 34

    I am trying to put the CM# and get the Client Manager Name.
    The formula I am using in M7 is =INDEX(C3:E10,MATCH(M6,E3:E10,0),3)

    When I put 71 in M6, the result I get is 71. I would like to get the name Sproule, Bob

    Does anyone know how to do this?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reverse Lookup (2000)

    See <post#=244408>post 244408</post#> by sdckapr about "VLookup to the left".

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reverse Lookup (2000)

    The column index argument of INDEX in your formula asks INDEX to pull off the value in E. That's why you get back what you're looking up.

    Since there could be duplicate names in C and D, like 2 Jones in C with diffrent first names in D, you'd better off if you create an additional column next to the CM.#.

    That is:

    In E enter & copy down:

    =C3&CHAR(127)&D3

    Now you can use either a VLOOKUP formula...

    =SUBSTITUTE(VLOOKUP(M6,$E$3:$F$10,2,0),CHAR(127),C HAR(32))

    or an INDEX/MATCH formula:

    =SUBSTITUTE(INDEX$F$3:$F$10,MATCH(M6,$E$3:$E$10,0) ),CHAR(127),CHAR(32))
    Microsoft MVP - Excel

Posting Permissions

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