VLOOKUP on first and last name with no unique ID

3
votes

Situation:

I have two sheets: The first sheet contains a list of employee names; the second sheet contains a piece of a pay report for employees. I need to pull the employee pay data from sheet 2 to sheet 1 by searching for the employee on sheet 2. What complicates this is the report on sheet 2 contains changing employees, pay, and has no unique ID.

I am looking to fill in column A. I cannot permanently modify this sheet as it affects downstream reporting.

I split column B which was previously first name, middle initial (if any), last name. Note, about 10% of the names may have a different last name. For example, sheet 1contains the name Tara Feather, but the second report uses her maiden name of Tara Bird.

Attempts so far:

I am new to Excel, and am just learning VLOOKUP. My first attempt was to pull the data based on the last name column on sheets 1 and 2, and enter data. Unfortunately, while that worked for 90% of the entries, the last 10% had the wrong values and the audit to correct took more time than manual entry.

Possible Ideas:

My next thought was to combine an If statement and VLOOKUP so that if last name matched, and first name matched, then execute the VLOOKUP and pull values, or enter "ERROR".

With this new thought, I don't know if I'm now in macro territory or if there are a combination of functions that can help. Ideally, the more it can do with 100% accuracy the better. However, if it can highlight the names it completed on sheet 2, I can always enter the non-highlighted manually, still saving time.

What are your thoughts and direction?

1
vbaexcelvlookup

1 Answers

2
votes

You want 100% accuracy, but if someone has different names on the two sheets, then that's never going to work.

So to get 90% of it right:

On the second sheet put this formula in G2 and copy it down for as many rows as you have names.

=B2&D2

Put this formula in H2 and copy it down

=F2

On the first sheet put this formula in A2 and copy it down

 =VLOOKUP(D2&C2,'second sheet'G:H,2,false)

Any rows where it can't find a matching firstname and surname will have #N/A in them.

Assuming you can modify the second sheet permanently you can put the right surnames in so that the formulae work. You could also put in the middle names into the formulae in case you have two people with the same names.