I have two tables:

1. Table with a list of sales with 3 parameters: CustomerID, Country and date of purchase.
2. Table with prices and Date range for each price

I want to see the updated price for each sale based on the date range of the price. How Can I do that?

I want to see 1100$ for the first sale and 1200$ to the 2nd sale

CodePudding user response：

Formula is:

=SUMIFS($C$2:$C$3;$A$2:$A$3;G2;$B$2:$B$3;H2;$D$2:$D$3;"<="&I2;$E$2:$E$3;">="&I2)

The tricky part here is the date criteria: your purchase date must be greater or equal to From Date and must be also lower or equal than To Date. Following that logic can help you out to track the price on a exact time period, country and client.

CodePudding user response：

Providing your Price From Date column is in ascending order you can use INDEX/MATCH: =INDEX($C$2:$C$3,MATCH(D6,$D$2:$D$3,1))

The 1 in the MATCH function tells it to "find the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order."