Link to home
Start Free TrialLog in
Avatar of jph826
jph826

asked on

vlookup using 2 cols as criteria

Excel 2003 - I have one  spreadsheet with 3 sheets.
sheet 1 is called 2011 contribs
sheet 2 is called 403 election
sheet 3 is called tds earnings

I need a formula to lookup earnings from sheet 3 and put on sheet 1.  The problem is that I need the formula to match 2 cols in sheet 3 with 2 cols in sheet 1 before returning earnings.
I need to match up emp (employee nbr) AND ppe (Pay period ending date) from sheet 3 and return the earnings onto sheet 1.  I would appreciate any suggestions.  Please see attached.

If emp_nbr
for-experts-2.xls
ASKER CERTIFIED SOLUTION
Avatar of StephenJR
StephenJR
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jph826
jph826

ASKER

Thanks a lot to you both!  Both formulas work but stop working at row 77 and return 0 for the rest of the spreadsheet which is 13,000 rows.  What am I doing wrong?
In mine, I should have locked the references - could that be it?

=SUMPRODUCT(('TDS earnings'!A$2:A$25='2011 contribs'!A2)*('TDS earnings'!B$2:B$25='2011 contribs'!G2)*('TDS earnings'!C$2:C$25))

However, Barry did so not sure!
Can you post the exact formula you used? You need to use $ signs in the ranges to stop those changing as you copy down (as I have done with 'TDS earnings'!A$2:A$100, for example)

barry
Avatar of jph826

ASKER

I'm sorry, I must be doing something wrong.  I can get them both to work but only up to a certain point.  Then one formula returns 0 and the other returns NA.  I'm attaching the whole file and so appreciate your help!
for-experts-all-data-2.xls
You needed to update the ranges to cover all data in the third sheet:

=SUMPRODUCT(('TDS earnings'!A$2:A$22264='2011 contribs'!A2)*('TDS earnings'!B$2:B$22264='2011 contribs'!G2)*('TDS earnings'!C$2:C$22264))
Avatar of jph826

ASKER

Thank you both so much.  Both formulas worked and I actually used one to validate the other.  I'm splitting the points between you.  Is there a way for me to remove the attachment?
If you hit the "Request Attention" button a moderator may be able to remove the attachment for you

regards, barry