Match Values for repeating Employee IDs

[rwmhr]

Hello,
I am in need of a formula that will allow me to compare two datasets with repeating employee IDs. The Task: I have to audit payments to employees. There is one sheet with a list of employee IDs and the payment they should receive, and another sheet from a system report listing the payments entered for the same employees. I have tried to use VLookup but it does not work when an employee is listed multiple times in either sheet. For example, John Smith may receive three payments of 500, 300, 1000. In both reports this will show as three rows with the different payments. However, when I use Vlookup to compare the two sheets, it shows John Smith with three rows but all payments show as 500. It seems to take the first value for John Smith and plugs it in the other two rows. See sample pic. Also, the rows in either sheets do not align row for row and not all employees may show in the system generated report sheet. The formula has to be able to use the employee's ID and the money value from one sheet and match it in the other sheet.
Thank you in advance for any help.

 

[AhoyNC]

Maybe something like this.

Book2
	A	B	C	D	E	F	G	H
1	To be paid Dataset A		To Pay	Paid		Paid in System Dataset B
2	111111	Employee1	650	650		111111	Employeel	650
3	222222	Employee2	500	500		222222	Employee2	500
4	333333	Employee3	750	750		555555	Employee5	800
5	555555	Employee5	800	800		333333	Employee3	750
6	555555	Employee5	600	Not Paid		555555	Employee5	500
7	555555	Employee5	300	300		555555	Employee5	300
8	777777	Employee7	1000	1000		444444	Employee8	550
9	444444	Employee8	600	Not Paid		777777	Employee7	1000
10	444444	Employee8	550	550
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=FILTER($H$2:$H$9,(A2=$F$2:$F$9)*(C2=$H$2:$H$9),"Not Paid")