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 employee 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 deducted by the employee or contributed
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),

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.

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.