How to get the latest status for an ID from an unsorted log
Formula (Excel and Google Sheets)
=XLOOKUP(1,(A2:A10=F1)*(B2:B10=MAXIFS(B2:B10,A2:A10,F1)),C2:C10)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).
- Result on the example
- Resolved in
F2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
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.
=XLOOKUP(1,(A2:A10=F1)*(B2:B10=MAXIFS(B2:B10,A2:A10,F1)),C2:C10)Result"Resolved"
A2:A109 rows × 1 col: "T-1042", "T-1043", "T-1042", "T-1044", …A range: 9 cells in one column.
F1"T-1042"The value in F1.
A2:A10=F1{9 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
B2:B109 rows × 1 col: 5/3/2026, 5/4/2026, 5/9/2026, 5/5/2026, …A range: 9 cells in one column.
MAXIFS(B2:B10,A2:A10,F1)46151MAXIFS: Largest value that meets every condition.- max_range
B2:B109 rows × 1 col: 5/3/2026, 5/4/2026, 5/9/2026, 5/5/2026, … - criteria_range1
A2:A109 rows × 1 col: "T-1042", "T-1043", "T-1042", "T-1044", … - criteria1
F1"T-1042"
- max_range
B2:B10=MAXIFS(B2:B10,A2:A10,F1){9 rows × 1 col} FALSE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
A2:A10=F1)*(B2:B10=MAXIFS(B2:B10,A2:A10,F1){9 rows × 1 col} 0, 0, 1, 0, …The * operator multiplies.
C2:C109 rows × 1 col: "Open", "Open", "Resolved", "Open", …A range: 9 cells in one column.
XLOOKUP(1,(A2:A10=F1)*(B2:B10=MAXIFS(B2:B10,A2:A10,F1)),C2:C10)"Resolved"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
1 - lookup_array
A2:A10=F1)*(B2:B10=MAXIFS(B2:B10,A2:A10,F1){9 rows × 1 col} 0, 0, 1, 0, … - return_array
C2:C109 rows × 1 col: "Open", "Open", "Resolved", "Open", …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(1,(A2:A10=F1)*(B2:B10=MAXIFS(B2:B10,A2:A10,F1)),C2:C10). We ran it in Google Sheets mode on the same data and it returns Resolved.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(C2:C10,MATCH(1,(A2:A10=F1)*(B2:B10=MAXIFS(B2:B10,A2:A10,F1)),0)) | Resolved | For Excel 2019 (Ctrl+Shift+Enter). |
| =INDEX(SORT(FILTER(B2:C10,A2:A10=F1),1,-1),1,2) | Resolved | 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.
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 latest status for an ID from an unsorted log
F2, return the most recent status of the ticket in F1.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).
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.