VLOOKUP match to Last Name OR Last Name, First Name

[kyle4570]

Please see the attached.

I end up with tons of text data in the Name column of the DataEntry Sheet, in either Last Name or Full Name (Last, First) form. I need a VLOOKUP formula in the Desired Results column of the DataEntry Sheet that will produce a match to the Employee ID from the Employee ID column of the Employee Master List Sheet, no matter which of the two forms of Name appear in the Name column of the Data Entry sheet.

In the case of data entry in the Name column of the Data Entry sheet, the VLOOKUP doesn't need to care about first name. Only looking to match to Employee ID if the last name is a match.

[alansidman]

In B2 and Copy Down. Be sure that your spelling and capitalizations are the same, ie. SomeBody <> Somebody

[kyle4570]

In B2 and Copy Down. Be sure that your spelling and capitalizations are the same, ie. SomeBody <> Somebody

Did you mean to attach a file and forgot to, or am I missing something? I appreciate your willingness to help.

[bebo021999]

Try one of below formula:

=INDEX('Employee Master List'!$C$2:$C$3,MATCH(TRUE,INDEX(ISNUMBER(SEARCH('Employee Master List'!$B$2:$B$3,$A2)),),0))

or

=INDEX('Employee Master List'!$C$2:$C$3,IFERROR(MATCH(A2,'Employee Master List'!$A$2:$A$3,0),MATCH(TRUE,INDEX(ISNUMBER(SEARCH(A2,'Employee Master List'!$A$2:$A$3)),),0)))

[alansidman]

No, I just forgot to post the formula. Bebo has you now so I will just back away and get some sleep.

[wk9128]

Worksheet name : Data Entry
Cell B2 formula , drag down

[kyle4570]

Did not mean to attach a file. Used your file.

Ah, didn't know you could do that.