Google-sheets – How to filter only the highest value for each date based in a criteria

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup

I have a table with 3 columns. I want to filter only the highest value of column B for each date (A) if column C contains the code "red".

My sheet:

date    entries code
08/04/2019  1   red
08/04/2019  3   red
07/04/2019  2   blue
07/04/2019  1   green
06/04/2019  6   red
06/04/2019  5   red
06/04/2019  4   blue

My query so far:

=QUERY(A2:C7, "select A, B where C = 'red'", 1)

My result:

08/04/2019  1
08/04/2019  3
06/04/2019  6
06/04/2019  5

But I would like to get only the highest value of each date:

08/04/2019  3
06/04/2019  6

I can even filter the highest value of B based on the dates of A (see below), but I can not filter the C code together.

=QUERY(A2:C7, "select A, max(B) group by A")

My question is: How to filter only the highest value of column B for each date (A) if column C contains the code "red"?

Best Answer

=ARRAYFORMULA(UNIQUE(VLOOKUP(
 QUERY(A2:C, "select A    where C = 'red' order by A desc, B desc", 0), 
 QUERY(A2:C, "select A, B where C = 'red' order by A desc, B desc", 0), {1,2}, 0)))

0