0

Based on the data below, I would like to get the latest date for each category where the type must not be "0"

enter image description here

Here is the result that I would like to show enter image description here

1 Answer 1

0

Assuming these things to be true:

  1. That your dates are actual dates that Google Sheets recognizes
  2. That your dates are chronologically entered (i.e., a cell's date is always earlier than all the dates below it)

Use this formula:

=ArrayFormula(IFERROR(VLOOKUP(UNIQUE(A:A),QUERY(IF(C:C<>0,A:C,""),"Select * Order by Col2 Desc"),{1, 2, 3},FALSE),""))

How it works:

Working from the inside out...

IF(C:C<>0,A:C,"")

First create a limited range of only those rows in A:C that don't have a zero value as "TYPE" (Column C).

QUERY(___,"Select * Order by Col2 Desc")

QUERY this range in reverse (now all "most recent dates" will be at the top.

VLOOKUP(UNIQUE(A:A),___,"Select * Order by Col2 Desc"),{1, 2, 3},FALSE)

Look up each unique value in A:A (in your example, there are only 4: A, B, C and null). Use the upsidedown QUERY we created as the search range. Return the array formed of all three columns ({1, 2, 3}, in other words, all data per matching row). The categories are not guaranteed in any order, so we use FALSE at the end.

Since VLOOKUP stops when it finds a match, and the QUERY range is upside-down, the first match will always be the one with the most recent date.

IFERROR(___,"")

If there is no match (which will only happen with the UNIQUE value null), leave that final row in the array null as well.

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.