Google Sheets

How to Get the Value for the Latest Date in Google Sheets

Published by OneCell

Use MAX to find the latest date in a Google Sheets log. Then use an exact XLOOKUP to bring back the value from the same row. This works when you add an older record at the bottom. Keep the date beside the value so you can tell how recent the entry is.

Set up a log with an older entry at the bottom

Create a tab called Pickups. Enter Recorded date in A1 and Open pickups in B1. Enter these fictional records using DATE so the dates are stored as values Sheets can compare. Google’s DATE reference

  • In A2, enter =DATE(2026, 11, 4). In B2, enter 14.

  • In A3, enter =DATE(2026, 11, 6). In B3, enter 9.

  • Leave A4 and B4 empty.

  • In A5, enter =DATE(2026, 11, 3). In B5, enter 12.

  • Leave A6 and B6 empty for the correction test.

Find the date and retrieve its value

Enter Latest recorded date in D1. Put this formula in D2 and format D2 as a date if it displays a number.

=IF(COUNT(A2:A6)=0, "", MAX(A2:A6))

COUNT checks for numeric entries in the date column. When there are none, IF displays a blank. Otherwise, MAX selects the greatest date value. Expect November 6, 2026 in D2. Google’s COUNT reference, IF reference and MAX reference

Enter Open pickups on that date in E1. Put this formula in E2.

=IF(D2="", "", XLOOKUP(D2, A2:A6, B2:B6, "", 0, -1))

If D2 displays a blank, E2 also displays a blank. Otherwise, XLOOKUP looks for D2 in A2:A6 and returns the corresponding value from B2:B6. The 0 requires an exact date match. The final -1 searches upward from the bottom, giving the bottommost match priority when dates tie. Expect 9 in E2. The older entry in row 5 leaves this result unchanged. Google’s XLOOKUP reference

Check a newer date, a correction and an empty log

  1. Change A5 to =DATE(2026, 11, 7) and keep B5 at 12. Expect November 7 and 12. Restore A5 to November 3.

  2. Suppose a lower row with the same date represents a correction in your log. Enter =DATE(2026, 11, 6) in A6 and 0 in B6. D2 still shows November 6 and E2 now shows 0, the value in row 6.

  3. Clear the practice entries in A2:B6. Keep the formulas in D2 and E2. Both results display blank. A recorded zero stays visible when a dated row supplies it.

Use date values in column A and a numeric value beside each date in column B. Leave both cells empty in unused rows. Use the bottommost match rule only if row order is how you record corrections. If you need to distinguish observations by time within one day, record full timestamps. When you add rows beyond row 6, extend the date range in D2 and both lookup ranges in E2 together.