Google Sheets – Get Data from Previous Row When Column Matches

google sheets

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

Best Answer

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.

Related Topic