Vlookup 2 Criteria

I am not sure if a vlookup will do the trick but it’s on the right track.
I have a spreadsheet with 2 workbooks, one is data imported from sap and the other is a manually created spreadsheet received by email. Both have corresponding data, id number, date. The SAP data has a column for number of hours. I need to pull up the number of hours where the date and the ID are the same.

Say for example
Worksheet a
A b c
Date ID Reg hours
01/01/2010 4 5.25
01/04/2010 4 8.25
01/05/2010 4 8.25



Worksheet b
A b c d
Date ID SickTime Reg Hours
01/01/2010 4 3


As you can see I would need worksheet “B” to look up “Reg Hours” based in the matching data in worksheet “A”. Would anyone be able to offer a suggestions?

I believe the SUMPRODUCT formula is the best for this application.

Modify the following formula to suit:

Please Login or Register  to view this content.

As a reference, the "A2:A5" represents the date column in the main database that you are querying (worksheet A). The "F2" represents the date cell in Worksheet B. "B2:B5" represents the ID field in Worksheet A, and G2 represents the employee ID in Worksheet B. Finally, "C2:C5" represents the employee hours column in Worksheet A. Let me know if you were able to adapt this.

just to confirm - the ID is only used once on each date?
If so, then the Sumproduct will work fine. If not, then you'll need another identifier to distinguish the unique entries but a sumproduct formlua will still be the one to use.

the id is the employee id, which will have several dates for each ID. its an employee time clock log.
so on worksheet b, each date will only have 1 date, but on worksheet a the ID's will many dates.

it works by the way, if there are additional dates for the id they just add up.

what if i added a 3rd criteria where there was a column in worksheet a that was for type, and i wanted to only produce the values that equal "Reg"?