There’s no built-in function in Google Sheets to retrieve the latest non-blank value by date—but you can achieve it using a custom formula made with native functions.

When working with multiple records (rows) related to a specific category (like a user or item), you may want to find the latest non-blank value among them based on date.

Real-Life Example

Let’s say you bought 1 liter of cooking oil on 25/07/2023, 06/08/2023, and 23/08/2023, with prices recorded as $3.52, $4.00, and blank (because you lost the bill).

In this case, the most recent price should not return a blank cell. Instead, it should return $4.00, the last known price.

This issue commonly occurs when using Google Forms to collect data into Google Sheets. For instance, a user might resubmit a form to update previously entered data.

These types of datasets typically include:

• A date or timestamp column
• A category column
• A values column (which may have blanks)

To look up the latest non-blank value by date in Google Sheets, we can use a combination of the FILTER, SORT, and VLOOKUP functions—or build a more advanced array formula.

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

Consider a table with three columns:

• Measured Date (A)
• Breed (B)
• Height in Inches (C)

To return the latest non-blank height per dog breed, enter this formula in cell F2:

=VLOOKUP(
   E2, 
   CHOOSECOLS(SORT(FILTER($A$2:$C, $C$2:$C<>""), 1, 0), 2, 3), 
   2, 
   FALSE
)

Then drag it down to apply to other breeds in the column.

Array Formula Version

To return values for multiple breeds at once, use:

=ArrayFormula(
   VLOOKUP(
      E2:E5, 
      CHOOSECOLS(SORT(FILTER(A2:C, C2:C<>""), 1, 0), 2, 3), 
      2, 
      FALSE
   )
)

This array formula returns the latest non-blank value by date in Google Sheets, specifically from the “Height in Inches” column, for each breed in the criteria range.

Formula Breakdown

Let’s understand how this formula works:

• FILTER(A2:C, C2:C<>""): Filters out rows where the value column (height) is blank.
• SORT(..., 1, 0): Sorts the filtered data in descending order by the date in column A (latest dates first).
• CHOOSECOLS(..., 2, 3): Rearranges columns so that VLOOKUP works correctly, making column B (Breed) the lookup column and column C (Height) the return column.
• VLOOKUP(E2, ..., 2, FALSE): Looks up the breed from cell E2 and returns the height from the most recent non-blank entry.

Notes

• E2 or E2:E5: Criteria range (breeds to look up)
• A2:C: The dataset range
• C2:C: The column from which we want the latest non-blank value (height)

Your dataset may vary, so understanding the logic behind the formula will help you adapt it to different scenarios.

Related Resources

• Lookup Latest Value in Excel and Google Sheets
• How to Lookup Latest Dates in Google Sheets
• Retrieve the Earliest or Latest Entry Per Category in Google Sheets
• Combine Rows and Keep Latest Values in Google Sheets
• Highlight the Latest Value Change Rows in Google Sheets
• How to Highlight the Latest N Values in Google Sheets
• Merge Duplicate Rows and Keep Latest Values in Excel