Index/Match for multiple fields, one of which is range?

[shellp]

I have two worksheets. One includes the patient ID and surgery date and the other has all dates for that patient ID but doesn't have surgery date, only admit and discharge date (so the surgery date is within this range). I wish to extract the visit ID for worksheet 1 from worksheet. I am familiar with vlookup but not match/index (assuming that is what is required here). Thank you for any assistance.

 

[ExceLoki]

try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Patient ID	Surgery Date			Patient ID	Visit ID	Start Date	End Date			Patient ID	Surgery Date	Visit ID
2	111	4/1/2022			111	666	1/1/2022	1/15/2022			111	4/1/2022	777
3	222	5/3/2022			111	777	3/29/2022	4/18/2022			222	5/3/2022	898
4	333	5/15/2022			111	888	7/1/2022	7/4/2022			333	5/15/2022	234
5					222	999	3/1/2022	3/3/2022
6					222	676	4/1/2022	4/15/2022
7					222	898	5/1/2022	5/3/2022
8					333	234	5/12/2022	5/16/2022
Sheet1

Cell Formulas
Range	Formula
L2:L4	L2	=VLOOKUP(K2,$A$2:$B$4,2,FALSE)
M2:M4	M2	=IFERROR(INDEX($F$2:$F$8,MATCH(1,(K2=$E$2:$E$8)*(L2>=$G$2:$G$8)*(L2<=$H$2:$H$8),0)),"")