# How to get the latest status for an ID from an unsorted log

Find the ID's latest date with MAXIFS and look up the row with that ID and date: `=XLOOKUP(1,(A2:A10=F1)*(B2:B10=MAXIFS(B2:B10,A2:A10,F1)),C2:C10)`.

**Excel:** `=XLOOKUP(1,(A2:A10=F1)*(B2:B10=MAXIFS(B2:B10,A2:A10,F1)),C2:C10)` (Excel 2021, 2024 and Microsoft 365)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Ticket | Updated | Status |  | Ticket | T-1042 |
| T-1042 | 2026-05-03 | Open |  | Latest status |  |
| T-1043 | 2026-05-04 | Open |  |  |  |
| T-1042 | 2026-05-09 | Resolved |  |  |  |
| T-1044 | 2026-05-05 | Open |  |  |  |
| T-1043 | 2026-05-06 | In progress |  |  |  |
| T-1042 | 2026-05-06 | In progress |  |  |  |
| T-1044 | 2026-05-08 | Waiting on customer |  |  |  |
| T-1043 | 2026-05-02 | New |  |  |  |
| T-1042 | 2026-05-02 | New |  |  |  |

Formula in F2: `=XLOOKUP(1,(A2:A10=F1)*(B2:B10=MAXIFS(B2:B10,A2:A10,F1)),C2:C10)`

Result: Resolved

## How it works

`MAXIFS(B2:B10,A2:A10,F1)` is the ticket's latest update date, May 9. The two multiplied tests are 1 only on the row with this ticket and that date, and XLOOKUP returns its status, Resolved. Because it works from the dates, the order of the rows doesn't matter.

## Other ways

- `=INDEX(C2:C10,MATCH(1,(A2:A10=F1)*(B2:B10=MAXIFS(B2:B10,A2:A10,F1)),0))`: For Excel 2019 (Ctrl+Shift+Enter).
- `=INDEX(SORT(FILTER(B2:C10,A2:A10=F1),1,-1),1,2)`: Filter the ticket's rows, sort them newest first and take the status from the first. Excel 2021 and Microsoft 365.

## Common mistakes

- Searching from the bottom with XLOOKUP's -1 gives the last row, which is the latest only if the log is sorted by date.
- Two updates on the same date tie, and the first one in the list wins. Record times as well as dates if that happens.

## Questions

**How do I list the latest status for every ticket?**

Put the tickets in a column with `=UNIQUE(A2:A10)` and use the formula next to it with a relative reference instead of F1.

**How do I get the date of the latest update too?**

That's the MAXIFS part on its own: `=MAXIFS(B2:B10,A2:A10,F1)`.

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/latest-status-for-an-id