1

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?

0

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.