Search for value based on 2 conditions (one of them a range of dates)

[Rosa_Bali]

Hi! I have 2x tables, let's call them:

PRICES. With the prices per room per night per hotel & their room categories, and based on date. For example, at Hotel "A" a Deluxe Room from 01 to 02 Jan costs $100. Or a Deluxe Room at Hotel "A" from 02 to 04 Jan costs $275 ($125 + $150).

QUOTATION. With an itinerary of "Day 1" - Stay at "xx Hotel/Room"; "Day 2" - Stay at "xx Hotel/Room". For ex., this quotation below would be for someone staying from 02 to 04 Jan at Hotel "A", at a Deluxe Room, 2 nights, $275 (as per previous example).

My app is based on "Quotation" table. I am trying to search for a formula to calculate something like:

Search on table "Quotation" for the date of [Day 1] among all the dates in table "Prices" from [01 Jan 2023] to [7 Jan 2023], and paralelly search on table "Prices" for the value of Table "Quotation" column [Hotel Option A] which matches on table "Prices" in column [Company,Service].

The same for Day 2.

The result on Table "Quotation", column [Total] should be, let's say, as the example, $275.

Someone can help please? @Steve and other kind colleagues, please.. 🙂 I've already tried all... 

This is my 3rd complex app already. Hopefully soon I am already ready to help others too 🙂 Thanks!!