How to find the most recent entry for an item in Excel
Logs grow downwards, so the most recent entry for an item is the last one in the list. A normal lookup finds the first one.
XLOOKUP’s sixth argument sets the search direction. -1 searches from the bottom up.
The formula
| A | B | C | |
|---|---|---|---|
| 1 | Date | Product | Price |
| 2 | 2026-01-05 | Toner | 42 |
| 3 | 2026-02-09 | Paper | 18 |
| 4 | 2026-03-02 | Toner | 45 |
=XLOOKUP("Toner",B2:B4,C2:C4,,0,-1)Result: 45
Toner appears twice. Searching from the bottom finds the March price, not the January one.
Step by step
- Write the XLOOKUP as usual: what to find, where to look, what to return.
- Add ,,0,-1 before the closing bracket.
- Press Enter.
Other ways to write it
=LOOKUP(2,1/(B2:B4="Toner"),C2:C4)An older trick that also finds the last match.
Each of these gives the same result on the example above, in Excel and in Cellwise.
Common mistakes
- Getting the oldest value
- Without -1, XLOOKUP stops at the first match, which in a log is the oldest.
- An unsorted log
- Searching from the bottom only finds the latest entry if the log is in date order.
Reading isn’t the same as doing
XLOOKUP’s hidden powers gives you real tasks on this, and tests your formulas on data you haven’t seen. It’s free, with no sign-up.