Printable View
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.
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
Thank you, but it only return the value of the first name, not the full name
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.
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
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.
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.
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.
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
cross posted at: Vlookup help needed
Please read this on crossposting. http://www.excelfox.com/forum/f25/me...1172/#post5326