Search code examples
google-sheetssearchreturn

Search multiple criteria in a Google sheet and return latest date

I am making a new sheet. B2:D2 - Cuvee, Sweet Pea, Zurkle (strains), A - Dispensary (https://docs.google.com/spreadsheets/d/11ele1sfanpaXYdC4Ng2eWZSPDCCliguEf_ndep_REYU/edit#gid=0)

I want to search another sheet (https://docs.google.com/spreadsheets/d/1SvVyTrHO9saAZrr9DGxrSUNjZr-Y1JfPdSuwng50QE8/edit#gid=0) for the following criteria: A: Product (B:D above) D: Dispensary, and E: Date and return the last/latest date in the new chart. i.e. return the last date a specific product was purchased by a specific dispensary into the new sheet.

I am new at this and can get the formula for searching the criteria, but do not know how to return the latest date.

Solution

  • Example

    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)