Google Sheets - Index Match multiple criteria and dates in headers

Question

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?

Answer 1

Use filter(), like this:

=map( 
  D9:D14, E9:E14, 
  lambda( 
    sku, date, 
    iferror( 
      filter( 
        filter(Data!B3:D, Data!B1:D1 <= date, date <= Data!B2:D2), 
        sku = Data!A3:A 
      ) 
    ) 
  ) 
)

If the data source is in the same tab as the result table, you can omit the sheet reference to Data!.