Left right VLOOKUP Help?

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

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!

Originally Posted by 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

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

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!

Originally Posted by 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