Solved

vlookup using 2 cols as criteria

Posted on 2011-02-18
9
336 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:jph826
  • 3
  • 3
  • 3
9 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 250 total points
Comment Utility
If only one line for each, this perhaps;

=SUMPRODUCT(('TDS earnings'!A2:A25='2011 contribs'!A2)*('TDS earnings'!B2:B25='2011 contribs'!G2)*('TDS earnings'!C2:C25))
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
Comment Utility
A lookup formula would also work, i.e.

=LOOKUP(2,1/(A2='TDS earnings'!A$2:A$100)/(G2='TDS earnings'!B$2:B$100),'TDS earnings'!C$2:C$100)

Advantages over SUMPRODUCT

would also work if the value to be returned is text
can't sum multiple values (if more than one row matches it returns data from the last one)

Disadvantages

Gives an error (rather than zero) if there is no matching row (although the formula could be modified to avoid that)

regards, barry
0
 

Author Comment

by:jph826
Comment Utility
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?
0
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
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!
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
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
0
 

Author Comment

by:jph826
Comment Utility
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
0
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
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))
0
 

Author Comment

by:jph826
Comment Utility
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?
0
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
If you hit the "Request Attention" button a moderator may be able to remove the attachment for you

regards, barry
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now