Vlookup help needed

[AbuReem]

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.

[alansidman]

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

[AbuReem]

Thank you, but it only return the value of the first name, not the full name

[alansidman]

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.

[AbuReem]

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

[alansidman]

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.

[AbuReem]

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.

[alansidman]

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.

[AbuReem]

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

[alansidman]

cross posted at: Vlookup help needed (http://www.excelforum.com/excel-formulas-and-functions/967027-vlookup-help-needed.html)

Please read this on crossposting. http://www.excelfox.com/forum/f25/message-to-cross-posters-1172/#post5326

[AbuReem]

alan

check the below link, maybe you could understand it better than me & come up with a solutions for me. thanks

Excel Magic Trick 729: VLOOKUP When Multiple Lookup Items In Cell, LOOKUP & SEARCH functions - YouTube (http://www.youtube.com/watch?v=0U2hudPVyAk)

[alansidman]

That video is returning one lookup value that is in a string of values. Not what you have. You are looking for multiple lookups from within the string -- opposite of what you are looking for. By the way, what is the matter with the solution provided to you at EF?

[AbuReem]

I already know ARGK formula, and i didn't like it, i know there is a better way to do it, that why I'm searching the net & ramananhrm formula is too complicated,i thank them for their efforts but that is not what I'm looking for. what you don't know is that in the source file i have more that 20000 values. that what makes there ways is not efficient. let me ask you this i have name in a column & code in another column let say John in A1 & J1 in B1, and you want to combine them in one cell to be shown like this John (B1), how would you do it. I'm asking that because someone send me a vb Formula which work , but i need to move all the 20000 values in source sheet to one cell to be read as John (B1).Thanks

[alansidman]

I am confused by your question. Can you post a sample spreadsheet showing the BEFORE and AFTER scenario. With regards to your original question, I do not have a solution for you.

[Bjarnehansen]

In a10 and a11 you have same name Gurjar

[AbuReem]

thats a mistake , you can delete one of them, thanks