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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
=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
barry
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
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='201 1 contribs'!A2)*('TDS earnings'!B$2:B$22264='201 1 contribs'!G2)*('TDS earnings'!C$2:C$22264))
=SUMPRODUCT(('TDS earnings'!A$2:A$22264='201
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
regards, barry
ASKER