Left right VLOOKUP Help?

[hoganc]

Hi All,

I am trying to find out how to do a V Lookup in excel the data in the two comparisons are not completely the same ie

ken Brett
Kenneth Brett
C Naylor
Colin Naylor

the surname in the cell is always correct but the first name isnt is there any way i can do a lookup on the name before the space counting from the right and also at the same time make it match the first letter of the name on the left so v lookup would search for

C Naylor or K Brett

i dont mind doing some of this manually but there 17559 of them altogether.

any ideas would be appreciated.

the normal v lookup as a false come up na for all of them and a true gives me the wrong information


Thanks

[Domenic]

Try the following...

=VLOOKUP("K*Brett",A1:B10,2,0)

OR

=VLOOKUP(C1&"*"&D1,A1:B4,2,0)

...where C1 contains the first name initial and D1 contains the surname.

Hope this helps!

Hi All,

I am trying to find out how to do a V Lookup in excel the data in the two comparisons are not completely the same ie

ken Brett
Kenneth Brett
C Naylor
Colin Naylor

the surname in the cell is always correct but the first name isnt is there any way i can do a lookup on the name before the space counting from the right and also at the same time make it match the first letter of the name on the left so v lookup would search for

C Naylor or K Brett

i dont mind doing some of this manually but there 17559 of them altogether.

any ideas would be appreciated.

the normal v lookup as a false come up na for all of them and a true gives me the wrong information


Thanks

[hoganc]

Hi there,

thanks for the solution you gave me however it is not working.

sheet1 column A i have the names

c naylor

in sheet2 column A i have the names

colin naylor
etc

i need to find a code that will look up any name not just naylor ie bob baggins if it is on sheet 2, i want the what is in column what is displayed on coumn b sheet 2 to be displayed on sheet 1 column 2

if thats possible, so I need it to do a look up on the surname on the right hand side of the cell and the first letter from the left

thanks anyone in advance

[Domenic]

Try the following...

On Sheet1, enter the following formula in B1 and copy down:

=VLOOKUP(LEFT(A1)&"*"&RIGHT(A1,LEN(A1)-FIND(" ",A1)),Sheet2!$A$1:$B$100,2,0)

...where A1 contains the lookup value. The lookup value can be entered as...

C Naylor

OR

Colin Naylor

If the lookup value is not entered in the proper format, you'll get a #VALUE! error. For this reason, you may want to consider using the following formula instead...

=IF(ISERR(FIND(" ",A1)),"Not entered in proper format!",VLOOKUP(LEFT(A1)&"*"&RIGHT(A1,LEN(A1)-FIND(" ",A1)),Sheet2!$A$1:$B$10,2,0))

Hope this helps!

Hi there,

thanks for the solution you gave me however it is not working.

sheet1 column A i have the names

c naylor

in sheet2 column A i have the names

colin naylor
etc

i need to find a code that will look up any name not just naylor ie bob baggins if it is on sheet 2, i want the what is in column what is displayed on coumn b sheet 2 to be displayed on sheet 1 column 2

if thats possible, so I need it to do a look up on the surname on the right hand side of the cell and the first letter from the left

thanks anyone in advance