Lookup value on another sheet where in date range

[BigRods]

Hi,
Hoping someone could help: I have a list of patient No's and the dates of their stay. Patients could have multiple stays - if they are still here then the Deduction Date is blank.

I then have another dataset of drugs prescribed; using the Start Date of the drugs I'd like to identify which Stay the prescription was under

Data looks as follows - in the Drugs Prescribed table, the 1st row for patient 123456 needs to have "Stay No" as 3 because the Start Date is between the Registration Date and Deduction Date of the Stays table, the Lookup would need to ignore the first entry for patient 123456:

Stays

Patient ID	Registration Date	Deduction Date	Stay No
123456	10-Mar-21	25-Mar-21	1
987654	11-Mar-21		2
123456	26-Jun-21	15-Aug-21	3

Drugs Prescribed

Patient ID	Drug	Start Date	Stay No
123456	Paracetamol	28-Jun-21	3
987654	Ibuprofen	15-Mar-21	2

Many thanks for any help - would this be some kind of INDEX/MATCH formula? And could it deal with instances of the End Date being blank?

 

[Toadstool]

Hi BigRods,

The tricky bit was handling the empty dates for Decuction Date (hence the extra data row for testing) but I think this does what you ask.

BigRods.xlsx
	A	B	C	D
1	Patient ID	Registration Date	Deduction Date	Stay No
2	123456	10-Mar-21	25-Mar-21	1
3	987654	11-Mar-21	12-Mar-21	2
4	123456	26-Jun-21	15-Aug-21	3
5	987654	11-Mar-21		4
6
Stays

BigRods.xlsx
	A	B	C	D
1	Patient ID	Drug	Start Date	Stay No
2	123456	Paracetamol	28-Jun-21	3
3	987654	Ibuprofen	15-Mar-21	4
Drugs Prescribed

Cell Formulas
Range	Formula
D2:D3	D2	=IFERROR(IF(A2="","",INDEX(Stays!$D$2:$D$11,AGGREGATE(15,6,ROW(Stays!$A$2:$A$11)-ROW(Stays!$A$1)/((Stays!$A$2:$A$11=A2)*(C2>=Stays!$B$2:$B$11)*((Stays!$C$2:$C$11="")+(C2<=Stays!$C$2:$C$11))),1))),"No matching Stay")

 

[Alex Blakenburg]

I haven't included all the error handling that Toadstool has but here are 2 other options.
The formula in D relies on the Stays list being in Ascending day order and it also won't tell you if scripts dates fall outside of stay dates.

20210920 Lookup MultiCriteria and Blank Date.xlsx
	A	B	C	D	E
1	Patient ID	Drug	Start Date	Stay No (Sorted Stay List)	Stay No (Unsorted Stay List)
2	123456	Paracetamol	28-Jun-21	3	3
3	987654	Ibuprofen	15-Mar-21	2	2
4	123456	Paracetamol	25-Mar-21	1	1
5	987654	Ibuprofen	28-Jun-21	2	2
6
Drugs Prescribed

Cell Formulas
Range	Formula
D2:D5	D2	=LOOKUP(2,1/((Stays!$A$2:$A$5=$A2)*(Stays!$B$2:$B$5<=C2)),Stays!$D$2:$D$5)
E2:E5	E2	=LOOKUP(2,1/((Stays!$A$2:$A$5=$A2)*(Stays!$B$2:$B$5<=C2)*((Stays!$C$2:$C$5>=C2)+(Stays!$C$2:$C$5=""))),Stays!$D$2:$D$5)

 

[BigRods]

Hi @Toadstool & @Alex Blakenburg - both options work great, thanks so much!

Sorry Alex, I guess Toadstool gets the solution as they were first

 

[Alex Blakenburg]

Haha. No problem. Thanks for the feedback.

 

[Toadstool]

Hi @Toadstool & @Alex Blakenburg - both options work great, thanks so much!

Sorry Alex, I guess Toadstool gets the solution as they were first

You're welcome!
Thanks for the feedback.