Excel Vlook up formula help

[Mallesh23]

 [LEFT][COLOR=#222222][FONT=Verdana]Hi Team,:confused:

[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]I want to apply a vlook up formula, but it will not work, as in my vlookup formula
LastName has come first and First name as last. and in mydata its opposite.[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]
I come cross such type of scenerio in office and manually update Teams score.[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]
Can you plz suggest a excel formula to deal with such situation. Thanks. 

[B]MY Data[/B]

[TABLE="width: 463"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Test1
[/TD]
[TD]Test2
[/TD]
[TD]Test3
[/TD]
[/TR]
[TR]
[TD]Sachin Tendukar
[/TD]
[TD]81
[/TD]
[TD]117
[/TD]
[TD]101
[/TD]
[/TR]
[TR]
[TD]Rahul Dravid
[/TD]
[TD]105
[/TD]
[TD]110
[/TD]
[TD]132
[/TD]
[/TR]
[TR]
[TD]Virendra Sehwag
[/TD]
[TD]106
[/TD]
[TD]132
[/TD]
[TD]51
[/TD]
[/TR]
[TR]
[TD]Rohit Sharma
[/TD]
[TD]73
[/TD]
[TD]74
[/TD]
[TD]93
[/TD]
[/TR]
[TR]
[TD]M Dhoni
[/TD]
[TD]87
[/TD]
[TD]116
[/TD]
[TD]104
[/TD]
[/TR]
[TR]
[TD]Kapil Dev
[/TD]
[TD]91
[/TD]
[TD]102
[/TD]
[TD]104
[/TD]
[/TR]
</tbody>[/TABLE]


[B]I am looking for result below here with vlookup / any other excel formula [/B]


[TABLE="width: 463"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Test1[/TD]
[TD]Test2[/TD]
[TD]Test3[/TD]
[/TR]
[TR]
[TD]Tendulkar Sachin[/TD]
[TD]=VLOOKUP(D13[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dravid Rahul[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sehwag Virendra[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Sharma Rohit[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dhoni M[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dev Kapil[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


Thanks 
Mallesh Gangadhar

[/FONT][/COLOR][/LEFT]

 

[Nishant94]

Unknown
	D	E	F	G
2	Name	Test1	Test2	Test3
3	Tendulkar Sachin	81	117	101
4	Dravid Rahul	105	110	132
5	Sehwag Virendra	106	132	51
6	Sharma Rohit	73	74	93
7	Dhoni M	87	116	104
8	Dev Kapil	91	102	104
9
10
11	Name	Test1	Test2	Test3
12	Sachin Tendulkar	81	117	101
13	Rahul Dravid	105	110	132
14	Virendra Sehwag	106	132	51
15	Rohit Sharma	73	74	93
16	M Dhoni	87	116	104
17	Kapil Dev	91	102	104
Sheet5

Cell Formulas
Range	Formula
E3	=VLOOKUP(TRIM(RIGHT(SUBSTITUTE($D3," ",REPT(" ",LEN($D3))),LEN($D3)))&" "&LEFT($D3,FIND(" ",$D3)-1),$D$12:$G$17,MATCH(E$2,$D$11:$G$11,0),0)

 

[Mallesh23]

Hi Nishant,

Thanks for quick reply,

how it works?. Can you explain it, I am not getting it looking at formula.

In which cell I have to put the formula?.... which range it has to look. ​

also in name if there is a , in between name instead of space

Regards,
Mallesh