Get the Latest Non-Blank Value by Date in Google Sheets

Non-Array Formula to Get the Latest Non-Blank Value by Date in Google Sheets

Let’s start with a simple example. Imagine you have a table with three columns: a date column, a category column, and a third column with values. You want to find the latest value for a specific category. Here’s how you can do it with a non-array formula:

=INDEX(SORT(FILTER(HSTACK(A2:A,C2:C),(B2:B=E2)*(C2:C>0)),1,0),1,2)

In this formula:

A2:A is the range of cells containing the dates.

B2:B is the range of cells containing the category names.

C2:C is the range of cells containing the values.

E2 is the cell that contains the category name you want to retrieve the latest value for.

Simply copy and paste this formula, and it will return the latest non-blank value for the specified category. You can even extend this formula to retrieve values from multiple columns.

Array Formula to Get the Latest Non-Blank Value by Date in Google Sheets

If you prefer using array formulas, I’ve got you covered too! With an array formula, you don’t need to specify the category names in a separate cell. The formula will take care of everything for you. Here’s the array formula you can use:

=LET(range,A2:C,distinct,UNIQUE(CHOOSECOLS(range,2)),IFERROR(HSTACK(distinct,MAP(distinct,LAMBDA(r, BYCOL(CHOOSECOLS(SORT(FILTER(HSTACK(CHOOSECOLS(range,1),CHOOSECOLS(range,{3})),CHOOSECOLS(range,2)=r),1,0),{2}),LAMBDA(c,CHOOSEROWS(TOCOL(c,1),1))))))))

In this formula:

A2:C is the range of cells containing the data, where A2:A contains the dates, B2:B contains the categories, and C2:C contains the values.

Similar to the non-array formula, this array formula can also retrieve the latest non-blank value from multiple columns. To do that, simply make the following changes to the formula:

Replace the range A2:C with A2:D.

Change CHOOSECOLS(range, {3}) to CHOOSECOLS(range, {3, 4}) if you have an additional column with values.

Adjust the array {2} to include the columns you want to retrieve values from.

And that’s it! You now know how to retrieve the latest non-blank value by date in Google Sheets using either a non-array formula or an array formula. Remember, these formulas are a game-changer when you have multiple data records for a category and need to find the most recent non-blank value. Happy sheeting!