Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    vlookup for name

    Hello-I need to create a formula to lookup, but the lookup value contains the stafff title and the table array does not include it. For example, vlookup value is Joe Doe ,MFT. how can I create a formula to give me the value I want. Thanks for your help.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Sheba,

    Is this what you are after?
    B2: =VLOOKUP((LEFT(A2,SEARCH(",",A2)-1)),Sheet2!A2:B5,2)
    FancyVLookup.JPG
    Note: If there is are spaces before the comma you'll need to enclose in a Trim function:
    B2: =VLOOKUP((Trim(LEFT(A2,SEARCH(",",A2)-1))),Sheet2!A2:B5,2)

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Seba,

    Another approach is an Index/Match array formula:

    Cell B12: {=INDEX(B4:B9,MATCH("MFT",RIGHT(A4:A9,3),0))}

    index_Match.png

    Enter the formula =INDEX(B4:B9,MATCH("MFT",RIGHT(A4:A9,3),0)) into cell B12 then press CTRL-Shift-Enter

    HTH,
    Maud

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    A variation of the sample above would be to replace the lookup value in the match formula with a cell reference:

    B12= {=INDEX(B4:B9,MATCH(A12,RIGHT(A4:A9,3),0))}

    By changing the value in A12 with another title, the associated value will be returned in B12.

    Maud

Posting Permissions

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