Search multiple criteria in a Google sheet and return latest date-CodePudding

I am making a new sheet. B2:D2 - Cuvee, Sweet Pea, Zurkle (strains), A - Dispensary (

Assume that your need to place data in a separate spreadsheets, here is the answer:

Place this formula in 'B3' of your 'Chart 2' spreadsheet.

Since this formula uses IMPORTRANGE() function to get your source data, you may need to Accept it to load data from other spreadsheet on the 1st run.

=LAMBDA(SOURCE,DISPENLOOKUP,PRODUCTLOOKUP,
 LAMBDA(PRODUCT,DISPEN,DATE,
  MAKEARRAY(COUNTA(DISPENLOOKUP),COUNTA(PRODUCTLOOKUP),LAMBDA(ROWINDEX,COLINDEX,
   IFERROR(TEXT(MAX(
    FILTER(DATE,SEARCH(INDEX(DISPENLOOKUP,ROWINDEX),DISPEN),SEARCH(INDEX(PRODUCTLOOKUP,COLINDEX),PRODUCT))
   ),"dd/mm/yyyy"),"")
  ))
 )(INDEX(SOURCE,,1),INDEX(SOURCE,,2),INDEX(SOURCE,,5))
)(QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1SvVyTrHO9saAZrr9DGxrSUNjZr-Y1JfPdSuwng50QE8/edit#gid=0","Sheet1!A1:E"),"WHERE Col1 IS NOT NULL"),$A$3:$A,$B$2:$2)