Vlookup help needed

**AbuReem** · 11-08-2013, 04:14 AM

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** · 11-08-2013, 04:31 AM

**AbuReem** · 11-08-2013, 04:41 AM

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

**alansidman** · 11-08-2013, 06:36 AM

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** · 11-08-2013, 07:40 AM

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** · 11-08-2013, 06:46 PM

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** · 11-08-2013, 10:09 PM

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** · 11-08-2013, 11:18 PM

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** · 11-09-2013, 01:27 AM

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