Index/Match to find matching value based on date range and other identifier.

[Henbob6]

Good Morning -

I am trying to match an authorization number which was active at the time of service. The date ranges assigned to the authorizations numbers sometimes overlap. I would like to pull the authorization number based on earliest start date.

My data is on two different sheets:
SHEET1

COLUMN A COLUMN B
DATE of SERVICE Client #, CPT, Carrier*
6/23/2014 AB22 90837 ACME
8/25/2014 AB22 90837 ACME
10/13/2014 VV47 90837 ACME
11/21/2014 AB22 90837 ACME
12/1/2014 VV47 90837 ACME


LOOKUP
COLUMN A COLUMN B COLUMN C COLUMN D
Client #, CPT, Payer* Start Date End Date Auth #
AB22 90837 ACME 10/18/2014 1/18/2015 104333902
AB22 90837 ACME 7/18/2014 10/18/2015 104174225
VV47 90838 ACME 7/18/2014 10/18/2014 N/A-378
VV47 90838 ACME 10/18/2014 1/18/2015 N/A-396

So for AB22, Date of Service 6/23 should be N/A, 8/25 should be 104174225, and 11/21 should be 104333902.

Taking baby steps:
Sheet1 - C2
Match Client/CPT/Carrier
=MATCH(B2,Lookup!B:B,0)

Sheet1 - D2
INDEX Date of Service against Start/End Date
={INDEX(Lookup!$D$1:$D$40, SMALL(IF((Sheet1!A3>=Lookup!$B$1:$B$40)*(Sheet1!A3<=Lookup!$C$1:$C$40), MATCH(ROW(Lookup!$D$1:$D$40), ROW(Lookup!$D$1:$D$40))), ROW(AAA1)))}

(no data is in AAA1 - I got this formula from another site, and I'm parroting as best I can.)

I think my next step is to incorporate the MATCH by Client/CPT/Carrier into the INDEX formula, but I am stuck.

*(Combined 3 data columns into 1 for lookup purposes.)

 

[AhoyNC]

Maybe something like this.
Adjust ranges to match your data.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

In this example formula in C2 can just be copied down.

Excel Workbook
	A	B	C	D	E
1	DATE of SERVICE	Client #, CPT, Carrier*	Auth #
2	6/23/2014	AB22 90837 ACME	#N/A
3	8/25/2014	AB22 90837 ACME	104174225
4	10/13/2014	VV47 90838 ACME	N/A-378
5	11/21/2014	AB22 90837 ACME	104333902
6	12/1/2014	VV47 90838 ACME	N/A-396
7
8
9
10	Client #, CPT, Payer*	Start Date	End Date	Auth #
11	AB22 90837 ACME	10/18/2014	1/18/2015	104333902
12	AB22 90837 ACME	7/18/2014	10/18/2015	104174225
13	VV47 90838 ACME	7/18/2014	10/18/2014	N/A-378
14	VV47 90838 ACME	10/18/2014	1/18/2015	N/A-396
15
Sheet

 

[Henbob6]

Wow, thank you so much for your time, AhoyNC! You saved me hours of banging my head! I truly appreciate your help.

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.