Arrayformula multiple criteria and between dates

I am trying to write an autofilling arrayfomula in Google Sheets. The formula should look up a price from a lookup table into the main table using 4 criteria.I feel like this is something I should be able to do easily, but the dates are throwing me off.

I can do this with index match, filters, queries, and some combinations, but I can't seem to figure out how to do it in a manner that will autofill, as rows will be added to the table regularly.

Information:

• This is essentially a 4 criteria lookup (fruit, date greater than start date, date less than end date, Qty)
• There are no patterns with the pricing
• The prices are located in the lookup table under columns 1, 2, 3, 4, 5, 6
• The date from the main table should be between the start date and end date column in the lookup table

Link to Sheet

Main Table - Fill in Price column from the Lookup Table | Fruit | Date | Qty | Price | | -------- | -------- | -------- | -------- | | Apple | 1/10/2021 | 5 | | | Apple | 4/20/2021 | 3 | | | Orange | 7/19/2021 | 5 | | | Strawberry | 10/19/2021 | 5 | | | Grape | 1/19/2022 | 3 | | | Banana | 4/18/2022 | 1 | | | Orange | 7/11/2022 | 1 | | | Strawberry | 10/10/2022 | 6 | | | Grape | 1/13/2023 | 1 | | | Banana | 3/3/2023 | 2 | |

Lookup Table | Fruit | Start Date | End Date | 1 | 2 | 3 | 4 | 5 | 6 | | -------- | -------- | -------- | -------- | -------- | -------- | -------- | -------- | -------- | | Apple | 1/1/2019 | 1/31/2022 | 10 | 6 | 16 | 16 | 9 | 4 | | Orange | 1/1/2019 | 1/31/2022 | 4 | 11 | 1 | 6 | 11 | 17 | | Strawberry | 1/1/2019 | 1/31/2022 | 16 | 14 | 17 | 14 | 1 | 6 | | Grape | 1/1/2019 | 1/31/2022 | 20 | 11 | 10 | 15 | 20 | 3 | | Banana | 1/1/2019 | 1/31/2022 | 7 | 1 | 20 | 8 | 2 | 9 | | Apple | 2/1/2022 | 12/1/9099 | 12 | 17 | 2 | 13 | 9 | 14 | | Orange | 2/1/2022 | 12/1/9099 | 16 | 5 | 19 | 16 | 19 | 9 | | Strawberry | 2/1/2022 | 12/1/9099 | 19 | 5 | 2 | 2 | 19 | 11 | | Grape | 2/1/2022 | 12/1/9099 | 8 | 3 | 19 | 16 | 9 | 1 | | Banana | 2/1/2022 | 12/1/9099 | 1 | 6 | 11 | 15 | 12 | 15 |

Desired Result | Fruit | Date | Qty | Price | | -------- | -------- | -------- | -------- | | Apple | 1/10/2021 | 5 | 9 | | Apple | 4/20/2021 | 3 | 16 | | Orange | 7/19/2021 | 5 | 11 | | Strawberry | 10/19/2021 | 5 | 1 | | Grape | 1/19/2022 | 3 | 10 | | Banana | 4/18/2022 | 1 | 1 | | Orange | 7/11/2022 | 1 | 16 | | Strawberry | 10/10/2022 | 6 | 11 | | Grape | 1/13/2023 | 1 | 8 | | Banana | 3/3/2023 | 2 | 6 |

Have tried using vlookup+match, filter, byrow+lambda (new to this), but can't seem to get anything to autofill.

Prefer a formula to apps script at this stage.

You may try:

=map(A3:A,B3:B,C3:C,lambda(a,b,c,if(a="",,+filter(filter(Lookup!D2:I,Lookup!D1:I1=c),Lookup!A2:A=a,Lookup!B2:B<b,Lookup!C2:C>b))))