# How to get the value on the most recent date in Excel

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.

**Excel:** `=XLOOKUP(MAX(A2:A7),A2:A7,B2:B7)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Statement date | Balance |  | Latest balance |
| 2026-03-31 | 12480.55 |  |  |
| 2026-01-31 | 10925.1 |  |  |
| 2026-05-31 | 13910.4 |  |  |
| 2026-02-28 | 11760.05 |  |  |
| 2026-06-30 | 14385.9 |  |  |
| 2026-04-30 | 13050.75 |  |  |

Formula in E1: `=XLOOKUP(MAX(A2:A7),A2:A7,B2:B7)`

Result: $14,385.90

## 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.

## Other ways

- `=INDEX(B2:B7,MATCH(MAX(A2:A7),A2:A7,0))`: Works in every Excel version.
- `=INDEX(SORT(A2:B7,1,-1),1,2)`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/value-on-most-recent-date