Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Vlookup help needed

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Junior Member
    Join Date
    Nov 2013
    Posts
    9
    Rep Power
    0

    Vlookup help needed

    I'm a beginner user of lookup, I have two sheets the first one ( Data) contains names, the second Sheet ( Source) contain names & their codes, what i need to do is to use vlookup formula to transfer names in sheet one to code in source sheet2, I'm attaching a file for more clarification.
    Attached Files Attached Files

  2. #2
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    In B2 type: =VLOOKUP(LEFT(A2,FIND(" ",A2)-1),Source!$A$2:$B$45,2,FALSE)

    and copy down.

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://eileenslounge.com/viewtopic.php?p=320960#p320960
    https://eileenslounge.com/viewtopic.php?p=320957#p3209573
    https://eileenslounge.com/viewtopic.php?p=318868#p318868
    https://eileenslounge.com/viewtopic.php?p=318311#p318311
    https://eileenslounge.com/viewtopic.php?p=318302#p318302
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317704#p317704
    https://eileenslounge.com/viewtopic.php?p=317857#p317857
    https://eileenslounge.com/viewtopic.php?p=317541#p317541
    https://eileenslounge.com/viewtopic.php?p=317520#p317520
    https://eileenslounge.com/viewtopic.php?p=317510#p317510
    https://eileenslounge.com/viewtopic.php?p=317547#p317547
    https://eileenslounge.com/viewtopic.php?p=317573#p317573
    https://eileenslounge.com/viewtopic.php?p=317574#p317574
    https://eileenslounge.com/viewtopic.php?p=317582#p317582
    https://eileenslounge.com/viewtopic.php?p=317583#p317583
    https://eileenslounge.com/viewtopic.php?p=317605#p317605
    https://eileenslounge.com/viewtopic.php?p=316935#p316935
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317030#p317030
    https://eileenslounge.com/viewtopic.php?p=317014#p317014
    https://eileenslounge.com/viewtopic.php?p=316940#p316940
    https://eileenslounge.com/viewtopic.php?p=316927#p316927
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

  3. #3
    Junior Member
    Join Date
    Nov 2013
    Posts
    9
    Rep Power
    0
    Thank you, but it only return the value of the first name, not the full name

  4. #4
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    That is correct. You don't have any full names in your lookup sheet. How can Excel know to do that if you don't provide the information in your look ups? With the lookups the way you have them, how will excel know to distinguish one Chandra from another Chandra. What logic to apply there?

    Perhaps you should provide a sample of what the end result (prepared manually) will look like and maybe someone will be able to provide a solution. I suspect that it may be a complex VBA solution.

  5. #5
    Junior Member
    Join Date
    Nov 2013
    Posts
    9
    Rep Power
    0
    Dear Alan
    If u use the following formula

    =VLOOKUP(LEFT(A2,SEARCH(" ",A2)-1),Source!A$2:B$45,2)&" "&VLOOKUP(REPLACE(Data!A2,1,SEARCH(" ",Data!A2),""),Source!A$2:B$45,2)

    on the sample file , the result will be ( R5 S2), that is the first & second Name in A2 Cell in data sheet, you can try it yourself.
    so i believe their is a way to do it, I need the result to be ( R5 S2 R6 M2). thanks

  6. #6
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    using your technique, you will need to use the Mid function coupled with the Find function. Don't have time now. Maybe I'll have sometime over the weekend to relook at it. I suspect it will be a very long formula.

    My question remains. You have two values for Chandra. How do you expect Excel to know which one is correct? Excel does not have visionary abilities to read your mind.

  7. #7
    Junior Member
    Join Date
    Nov 2013
    Posts
    9
    Rep Power
    0
    Alan
    I have only one value for Chandra not two, beside it's easy to say that you don't know how it could be done, it's not wrong, we all here to learn something new. no one knows everything. anyhow thanks & appreciate your efforts.

  8. #8
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13
    There is no question that I don't have an off the cuff solution, however if you look in your sample spreadsheet at cells A6 and A7, there are indeed two Chandra's with different values associated with them. So I reiterate my previous question.

  9. #9
    Junior Member
    Join Date
    Nov 2013
    Posts
    9
    Rep Power
    0
    Alan
    You are right , there was two chandra, While there should be one code for each name, i delete one of them , so now chandra will always return the value as C2, but of cource you know that still didn't solve the problem. or get us closer to solve the problem! Thanks

  10. #10

Similar Threads

  1. formulas needed please
    By paul_pearson in forum Excel Help
    Replies: 5
    Last Post: 03-21-2013, 04:43 PM
  2. Indirect Address Column Formula help needed
    By trankim in forum Excel Help
    Replies: 4
    Last Post: 10-13-2012, 02:15 PM
  3. Replies: 11
    Last Post: 10-07-2012, 12:05 AM
  4. Sum Ifs Formula needed with Max Date range
    By trankim in forum Excel Help
    Replies: 2
    Last Post: 09-19-2012, 09:50 AM
  5. Replies: 12
    Last Post: 05-27-2012, 08:38 PM

Posting Permissions

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