Excel: HLOOKUP Text "approximate" match over 2 sheets problem?
 

Hi, Please help?
I have two sheets, the one is an extract from Payroll about employee details
with first, second, third and lastname + initials.
The second sheet I have Firstname & Lastname columns and then a column with
the job title. I need to match the names and then pull the job decriptions
over to the first sheet with the many names and detail and need to match them?
Problem is that with the many names you still have surnames with 3 words and
to sort and filter that take weeks if sheet 1 has 17000 rows and sheet 2 have
2200 rows.

Excel: HLOOKUP Text "approximate" match over 2 sheets problem?
 

Hi

Insert a new column A on both sheets.
In this column create a concatenation of First and Lastnames
=B1&"|"&C1 for your second sheet. Choose the appropriate columns for
Sheet1

On Sheet1, in the column where you want the Job title to appear
=VLOOKUP(A1,Sheet2!$A:$D,4,0)

After copying down as appropriate, copy the new column on Sheet1, Paste
SpecialValues to "fix" the data.
Then you can delete column A on both sheets.

--
Regards

Roger Govier


"Excel: Text (match, lookup...)" <Excel: Text (match,
wrote in message
...
Hi, Please help?
I have two sheets, the one is an extract from Payroll about employee
details
with first, second, third and lastname + initials.
The second sheet I have Firstname & Lastname columns and then a column
with
the job title. I need to match the names and then pull the job
decriptions
over to the first sheet with the many names and detail and need to
match them?
Problem is that with the many names you still have surnames with 3
words and
to sort and filter that take weeks if sheet 1 has 17000 rows and sheet
2 have
2200 rows.

Excel: HLOOKUP Text "approximate" match over 2 sheets problem?
 

Before trying to come up with some sort of convoluted formula, can't you
extract an employee ID/ salary ref number from payroll?
This would then let you use a simple VLOOKUP on the a unique identifier.

Cheers, Pete

"Excel: Text (match, lookup...)" wrote:

Hi, Please help?
I have two sheets, the one is an extract from Payroll about employee details
with first, second, third and lastname + initials.
The second sheet I have Firstname & Lastname columns and then a column with
the job title. I need to match the names and then pull the job decriptions
over to the first sheet with the many names and detail and need to match them?
Problem is that with the many names you still have surnames with 3 words and
to sort and filter that take weeks if sheet 1 has 17000 rows and sheet 2 have
2200 rows.

Excel: HLOOKUP Text "approximate" match over 2 sheets problem?
 

Yes I can bring the employee number into sheet 2 with the Job title, but
sheet 1 has no reference to the employee number?

"Pete McCosh" wrote:

Before trying to come up with some sort of convoluted formula, can't you
extract an employee ID/ salary ref number from payroll?
This would then let you use a simple VLOOKUP on the a unique identifier.

Cheers, Pete

"Excel: Text (match, lookup...)" wrote:

Hi, Please help?
I have two sheets, the one is an extract from Payroll about employee details
with first, second, third and lastname + initials.
The second sheet I have Firstname & Lastname columns and then a column with
the job title. I need to match the names and then pull the job decriptions
over to the first sheet with the many names and detail and need to match them?
Problem is that with the many names you still have surnames with 3 words and
to sort and filter that take weeks if sheet 1 has 17000 rows and sheet 2 have
2200 rows.