4

I'm using Google sheets and trying to find a prior service date for each location. I'd like to have a result look like the below. I already have data filled in for column A and B.

What formula should I use for column C?

COLUMN A          COLUMN B           COLUMN C 
DATE              LOCATION           PRIOR SERVICE DATE
10/21/2015        Bar & Grill      
11/12/2015        Bar & Grill        10/21/2015
11/24/2015        Bar & Grill        11/12/2015
7/16/2015         Asian Restaurant      
7/31/2015         Asian Restaurant   7/16/2015
8/12/2015         Asian Restaurant   7/31/2015
7/16/2015         Bistro       
7/31/2015         Bistro             7/16/2015
8/12/2015         Bistro             7/31/2015
10/28/2015        Drive In      
11/9/2015         Drive In           10/28/2015
11/24/2015        Drive In           11/9/2015
3

You can filter column A by the location name, keep only the dates preceding the one in the current row, and take the maximum of those. Like this:

=iferror(max(filter(A$2:A, B$2:B=B2, A$2:A<A2)))

The formula shown is for the cell C2; it can be copied to the rest of the C columns.

  • filter keeps only the dates prior to the current ones, where property name matches.
  • max find the maximal (latest) of those
  • iferror returns a blank (instead of an ugly #N/A error) when there is no prior date.

Be sure to format the columns A and C as dates.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.