Compare partial data in two columns Then copy number value into cell when match found

Hi all,

Been struggling with this formula for way too long. . I have one worksheet (VISITS) with names, birthdates and other data fields and a second worksheet (HEADERS) with the same name and birthdates. I need to match the name and birthdate columns on both sheets and when there is a match, copy the employee number from HEADERS onto a column in the VISITS sheet.

Now here's the hitch: the names in the HEADER sheet have about two or three additional characters at the end of the cell than the names on the VISIT sheet so most of the names will not be an exact match between the sheets. Example is SMITH, JAMES E in the header table and SMITH, JAME in the visit table. Also, the dates are in yyyymmdd format which may be causing me problems.

Comparing the last names and birthdays will match the correct records. Once that match is found, copy the customer number from Header table into the visit table. I have attached a sample of the file with the Header and Visit data.

Any help would be greatly appreciated!!

Rebel