I am trying an index formula so that I can retrieve the value of dataset based on the item and the date.
Data source formatted as below, with price valid until
Start | 01/Sept/2022 | 01/Oct /2022. | 29/Oct /2022. |
---|---|---|---|
End | 30/Sept/2022 | 28/Oct /2022. | 31/Dec/2022. |
AAAAA | 12 | 8 | 10 |
BBBBBB | 15 | 13 | 13 |
Table format where I want to retrieve the value, with the correct information, starting at cell ref D8.
SKU. | Date. | Value |
---|---|---|
AAAAA | 17/Sept/2022 | 12 |
AAAAA | 12/nov/2022 | 8 |
BBBBB | 12/nov/2022 | 13 |
12 for the first (price as of Sept 17, then 10 for the second row since price became 10 from October 29th. for the third line SKU BBBBB as of Nov 12 price is 13.
So in the column Value I have used the below formula
=INDEX(B$3:D$6,MATCH(D9,A$3:A$46,0),MATCH(TRUE,INDEX($B$1:$D$1>=E9,0),MATCH(TRUE,INDEX($B$2:$D$2<=E9,0),0)))
where D9 is the SKU ref (AAAAA or BBBBB) and E9 is the date.
It does not return the results expected. Any clue?