Return cell's value based on its date range in Excel

Question

I have a table with specific products and dates and I want to get the cost values that correspond to that date . The source table has a range of dates and not an actual match (that is my problem). Here is the task: we are trying to fill column "Cost" based on Sheet 2

SHEET 1:

Product	Date	Cost	price
First	29/12/2021	result 1 (formula type X)	100
Second	05/01/2021	result 2 (formula type X)	200

The other Sheet has the date ranges with the desired results (selling prices), like this:

SHEET 2:

Product	Start Date	End Date	Cost
First	28/12/2020	03/01/2021	result 1
Second	04/01/2021	11/01/2021	result 2

PS. I have different costs for different products in the same date. So, we needed to also add a parameter that will match the Product from one sheet with the product of the other.

Answer 1

If the given Ranges both start at A1 and end at D3 then the following works in Sheet1!C2: =INDEX(Sheet2!D:D,MATCH(1,(B2>Sheet2!B:B)*(B2<Sheet2!C:C)*(A2=Sheet2!A:A),0))

This is an array formula to be entered with ctrl + shift + enter

It Indexes sheet2 column D and searches for the first match where all mentioned condition are true (=1). Each condition produces 1 or 0 for each cell in the range and multiplies it by the result of the cell from the next range in the same row. If either of the conditions is false it multiplies by 0 resulting in 0. If all conditions are true it will result in 1 (111). The overall produces an array of {0,0,1,0,...} and the match function returns the N'th occurance of the first 1, which is equal to the row number of the conditions being true.

Answer 2

Since you mentioned tables I'm going to assume you mean a real Excel Table and not just cells formatted into a table like appearance.

• Sheet 1 Table is named: tbl_ProductPrice
• Sheet 2 Table is named: tbl_ProductCost

"Cost" column formula in sheet 1:

 =SUMIFS(tbl_ProductCost[Cost],[Date],">="&tbl_ProductCost[Start Date],[Date],"<="&tbl_ProductCost[End Date])

Explanation

First SUMIFS parameter, "Cost" column, is what will be summed up if all criteria are true.

First IF:

• Second parameter is the date criteria to check.
• Third parameter is what to check against, is greater than or equal to start date.

Second IF:

• Fourth parameter is the date again for the second if statement
• Fifth parameter is checking if less than or equal to the end date.

---

Results:

---

EDIT

Based on your comment regarding multiple product entries for different date ranges I would go with the Index Match approach instead.

=INDEX(tbl_ProductCost[Cost],MATCH(1,([@Product]=tbl_ProductCost[Product])*([@Date]>=tbl_ProductCost[Start Date])*([@Date]<=tbl_ProductCost[End Date]),0))

Enter formula with Ctrl+Shift+Entersince it's an array formula.

I added in a product match as well since you indicated multiple date ranges for each product type.

Results