# How to filter only the highest value for each date based in a criteria?

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"?

## 1 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)))``````

• Wow!! Exactly what I needed. Thank you very much for your help, my friend. Apr 8, 2019 at 16:06