can't figure out LOOKUP with 2 variables

[LTUser54]

I think this a HLOOKUP or INDEX problem for some pension numbers. I have 2 worksheets in different workbooks, both have employee names and amounts deducted by the employee or amounts contributed by employer. Not everybody has the same types of contributions; Pretax, Employer match, after tax and safe harbor amounts.

One worksheet from the pension vendor lists the employee name in a column and lists the name and dollar amount separately each time the employee contribution OR employer contribution appears.

The other worksheet lists only the employee name and then the amounts across rows, based on what is contributed by the employee or by the employer.

Describing the worksheets: the vendor worksheet has LN, FN, Type of contribution (Pretax, After tax, match, and safe harbor - not necessarily in that order by employee), and the Amount is listed in columns by LN Ascending.

The employer worksheet has a header of:LN, FN, and category (Employee After tax, Pretax, Match, and safe harbor), with the employee name, and a dash in any cell that does not have a dollar amount in it for the particular type of contribution. To complicate matters, each worrksheet has a different version of the name for the contribution. One might list "EMPLOYEE PRE-TAX" and the other might have "Pre-Tax Contribution" as the description.

Can you suggest a furmula that will allow me to check both employee name and the contribution amounts for even matching? I'm trying to find all variables between these lists.

I tried VLOOKUP matching separate types of contribtions sorted employee name ascending, but I'm really looking for one formula that allows the complete worksheets to be compared with each other without doing a lot of cutting & pasting of info.

If I need to add more info, please let me know. I can't figure this out.
Any help is greatly appreciated. Thanks.

Mark
Boston, MA

[mrice]

If I have understod the problem correctly, a simple solution is to add an extra column to both sheets which contains the concatenated values of the two columns that you are interested in.

e.g. set C1 as = A1 & B1

You can then use VLOOKUP on this columns to look for matches which have both the original values in common.