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.
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.