Index Match Using With Date Ranges

[jlenn10]

Hey - I am trying to do an index match for multiple criteria. My issue is that the 2nd criteria are date ranges. What I am trying to determine is if there is a match in the P2 cell and column C:C in the "Auths" sheet, then look to see if the date in cell J2 is within the date range of columns E:F on the "Auths" sheet. Note - there's 2 columns for a start date and an end date.

Any idea if I am able to do this through an index match? Here's what I've written so far:

=INDEX(Auths!C:C,MATCH('UMD074,UMD063,UMD010'!P2,Auths!D:D,0))

Thanks for your help

 

[Snakehips]

Please clarify.
Find P2 in Auth C:C?
If found, check to see if J2 Date is within the same Auth row, in range defined by Auth E:F?
Inclusive of start and end dates?
Return what, True / False / The row number ?

 

[jlenn10]

Sorry for the mistake -

If P2 (member ID) is in Auths D:D (member ID)
If found, check to see if J2 (date of service) is within the start and end date range on E:F in Auths. Inclusive of start and end dates.
If true, return the auth # in C:C in Auths.

Here's how the "Auths" tab looks (had to remove PHI due to HIPAA)

Col 1	Col 2	Auth Number	Member ID	Authorization Start Date	Authorization End Date
Redacted	Redacted	auth 1	member 1	2/17/20​	5/17/20​
Redacted	Redacted	auth 2	member 1	3/10/20​	6/8/20​
Redacted	Redacted	auth 3	member 1	3/6/20​	6/4/20​
Redacted	Redacted	auth 4	member 1	2/5/20​	5/5/20​
Redacted	Redacted	auth 5	member 1	9/15/20​	12/14/20​
Redacted	Redacted	auth 6	member 1	10/13/20​	1/11/21​
Redacted	Redacted	auth 7	member 2	10/1/20​	12/30/20​
Redacted	Redacted	auth 8	member 2	10/20/20​	1/18/21​
Redacted	Redacted	auth 9	member 2	10/15/20​	1/13/21​
Redacted	Redacted	auth 10	member 2	11/20/20​	2/18/21​
Redacted	Redacted	auth 11	member 2	11/20/20​	2/18/21​
Redacted	Redacted	auth 12	member 2	11/9/20​	2/7/21​
Redacted	Redacted	auth 13	member 2	12/15/20​	3/15/21​
Redacted	Redacted	auth 14	member 3	1/24/20​	4/23/20​
Redacted	Redacted	auth 15	member 3	2/20/20​	5/20/20​

 

[Snakehips]

Is this data typical?
member 2 and 16/10/20 matches Auth Numbers auth 7 and auth 9 !!!!!! Which rather complicates the issue.

 

[jlenn10]

In healthcare it is. The auth numbers span over a specific date range. Often times providers will get a new auth that overlaps the existing auth which causes confusion. I'm not worried about the overlap, I just need to prove that there was an auth on file for the member/date of service.

 

[Snakehips]

This should return the first instance.
Not sure how efficient it will be on large dat set.
Example is Excel2010 hence the Ctrl + Shift + Return

Book1
	I	J	K	L	M	N	O	P	Q	R
1		Date						Member ID		Authorisation
2		16/10/2020						member 2		auth 7
UMD074,UMD063,UMD010

Cell Formulas
Range	Formula
R2	R2	=INDEX(Auths!C:C,MATCH(TRUE,(Auths!D:D=P2)*(Auths!E:E<=J2)*(Auths!F:F>=J2)*(ROW(D:D))>0,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope it helps.

 

[jlenn10]

Thank You! I had to change the last section to (ROW(I:I))>P,P)) since P:P is the member ID in the main tab. I was able to get 85% of the results which is fine. Saved us 8+ hours of manual look ups.