Lookup with Multiple criteria within a date range or open ended date

[shameela patel]

Good Day please can you kindly assist with a formula, I have managed to find half of the formula, where the formula takes a check-out date and check-in date into account,
however I need a formula to take an open ended date into account. as well ,
where Check in Date ="0" .

The formula is looking up the RA from 2 different spreadsheet, taking the tracking Id , date range into account,
Please let me know if you have a simpler formula or 2 separate formulas needs to be used.

You will notice the example given below has both open end date and a check in date

The current formula I am using
LOOKUP(3,1/( 'ASR02'!$K:$K<E3)/( 'ASR02'!$U:$U>=E3)/( 'ASR02'!$B:$B= 'Voucher Engine Raw data'!J3), 'ASR02'!$E:$E)

TRACKING ID ASR SPREADSHEET	RA NO	CHECK OUT BRANCH	CHECK OUT DATE	CHECK IN DATE
1844​	190561516​	PEDT	16/09/2021​	20/09/2021​
1844​	190593474​	PEDT	26/09/2021​	0​

Date	Tracking ID	RA NUMBER
17/09/2021	1844​	190561516​
19/9/2021	1844​	190561516​
08/10/2021	1844​	#N/A​
15/10/2021	1844​	#N/A​
22/10/2021	1844​	#N/A​
27/09/2021	1844​	#N/A​
04/10/2021	1844​	#N/A​
05/10/2021	1844​	#N/A​
08/10/2021	1844​	#N/A​
12/10/2021	1844​	#N/A​
15/10/2021	1844​	#N/A​
19/10/2021	1844​	#N/A​
22/10/2021	1844​	#N/A​