Help with date formula???

[MHodgin1016]

I'm trying to build a PivotTable that will show us how many total units each of our clients has in their authorizations, how many units have been used, and how many are left. Every two weeks our payer sends us an "Auth Dump" that includes the client ID, authorization ID #, service name, start date of auth, end date of auth, and total units authorized. They also send us a "Claim Dump" which includes the client ID, date of service, service name, units billed, and if the billing was approved. I import both of these text files into separate workbooks.

I need to add a column to the Claim Dump table that indicates the authorization ID # associated with each claim. This would be found by matching the client ID # and service name on the Auth Dump and Claim Dump tables, then assessing which auth start and end date from the Auth Dump contains the date of service on the Claim Dump.

Any help would be greatly appreciated.

 

[MHodgin1016]

Maybe this will help things be a little clearer about what I need. I have the following two tables on separate worksheets:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Patient_ID	Authorization_ID	Service_code	Start_date	End_date	Auth_units	Units_used	Units_Remaining
2	1111	1234	H2013	1/1/2017	12/31/2017	6580
3	1111	8964	S5150	1/1/2017	12/31/2017	1200
4	2222	5678	H2013	3/1/2017	2/28/2018	7460
5	3333	9101	H2013	4/1/2017	3/31/2018	8420
6	3333	6791	S5125	4/1/2017	3/31/2018	5241
7	4444	1121	S5150	5/1/2017	4/30/2018	800
8	5555	3141	H2013	6/1/2017	5/31/2018	4456
9	5555	7316	S5150	6/20/2017	2/28/2018	6325
10	6666	5161	S5125	5/1/2017	4/30/2018	3712
11	7777	7181	H2013	4/24/2017	3/31/2018	500
Auth_Dump

Excel Workbook
	A	B	C	D	E	F
1	Patient_ID	Service_code	Date_of_service	Status	Units_billed	Authorization_ID
2	1111	H2013	3/18/2017	Approved	26
3	3333	S5125	5/25/2017	Approved	18
4	5555	H2013	6/26/2017	Approved	25
5	1111	H2013	5/25/2017	Approved	34
6	1111	S5150	2/16/2017	Approved	75
7	4444	S5150	5/6/2017	Approved	20
8	6666	S5125	5/18/2017	Approved	16
9	2222	H2013	3/4/2017	Approved	16
10	7777	H2013	5/15/2017	Denied	125
11	6666	S5125	5/26/2017	Approved	14
12	5555	H2013	6/2/2017	Approved	63
13	5555	S5150	6/23/2017	Approved	58
14	2222	H2013	4/14/2017	Approved	91
15	3333	H2013	4/14/2017	Approved	64
16	3333	S5125	4/15/2017	Denied	13
Claim_Dump

I need a formula in the Claim_Dump table that populates the correct authorization ID from the Auth_Dump table. A client may have multiple authorizations for the same service, but the dates should not overlap. Then I need a formula in the Auth_Dump table that sums the approved claims for that authorization ID.

I've been playing around with IF and INDEX MATCH MATCH, but so far no luck. Any ideas?