Excel data merging across excel files and matching fielddata

[Lorinc C]

I have two excel files each has unique data
1st file: SS, Last Name, First Name...
2nd file: Last Name, First Name, Employee Number

I need to copy Employee Number from the 2nd file to the corresponding employee.
An employee may be listed more than once in file 1.
Thank you , Lorinc


EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[RagDyeR]

I would assume that the possibility that you might have a Tom Jones *and* a
John Jones does exist, so you should check *both* first and last names.

Enter this *array* formula in D2 of Sheet1:

=INDEX(Sheet2!C$2:C$20,MATCH(1,(Sheet2!A$2:A$20=B2)*(Sheet2!B$2:B$20=C2),0))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

*After* the CSE entry, copy down to D20.

Adjust ranges to suit your datalist.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

<Lorinc C> wrote in message I have two excel files each has unique data
1st file: SS, Last Name, First Name...
2nd file: Last Name, First Name, Employee Number

I need to copy Employee Number from the 2nd file to the corresponding
employee.
An employee may be listed more than once in file 1.
Thank you , Lorinc


EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

 

[Lori]

It may be worth checking if names are repeated in the list. This
formula should give the last match in the list which you can compare
with the formula above which gives the first match:

=LOOKUP(2,1/(Sheet2!A$2:A$20=B2)/(Sheet2!B$2:B$20=C2),Sheet2!C$2:C$20­)