How to get the value on the most recent date in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(MAX(A2:A7),A2:A7,B2:B7)Look up the latest date and return the value beside it: =XLOOKUP(MAX(A2:A7),A2:A7,B2:B7). The list can be in any order.
- Result on the example
- $14,385.90 in
E1 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
How it works
Dates are numbers, so MAX returns the latest one, June 30. XLOOKUP finds that date in column A and returns the balance on its row, $14,385.90. The rows can be in any order.
=XLOOKUP(MAX(A2:A7),A2:A7,B2:B7)Result14385.9
A2:A76 rows × 1 col: 3/31/2026, 1/31/2026, 5/31/2026, 2/28/2026, …A range: 6 cells in one column.
MAX(A2:A7)46203MAX: Largest number.- number1
A2:A76 rows × 1 col: 3/31/2026, 1/31/2026, 5/31/2026, 2/28/2026, …
- number1
B2:B76 rows × 1 col: $12,480.55, $10,925.10, $13,910.40, $11,760.05, …A range: 6 cells in one column.
XLOOKUP(MAX(A2:A7),A2:A7,B2:B7)14385.9XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
MAX(A2:A7)46203 - lookup_array
A2:A76 rows × 1 col: 3/31/2026, 1/31/2026, 5/31/2026, 2/28/2026, … - return_array
B2:B76 rows × 1 col: $12,480.55, $10,925.10, $13,910.40, $11,760.05, …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(MAX(A2:A7),A2:A7,B2:B7). We ran it in Google Sheets mode on the same data and it returns $14,385.90.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(B2:B7,MATCH(MAX(A2:A7),A2:A7,0)) | $14,385.90 | Works in every Excel version. |
| =INDEX(SORT(A2:B7,1,-1),1,2) | $14,385.90 | Sorts newest first and takes the balance from the top row. Excel 2021, Microsoft 365 and Google Sheets (with FALSE for descending). |
Common mistakes
- Dates stored as text are ignored by MAX. Check that column A holds real dates.
- If two rows share the latest date, the first one is returned.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Get the value on the most recent date
E1, return the balance on the most recent statement date.Questions
How do I get the value on the oldest date?
Use MIN instead of MAX.
How do I get the latest value for one account?
Use MAXIFS for that account's latest date and match both; see the latest status for an ID.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.