Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to look up the most recent date for an item in Excel and Google Sheets
Topic:Lookups (any direction)Excel & Google Sheets
=MAXIFS($B$2:$B$5,$A$2:$A$5,"Apple")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Item | Date |
| Apple | 2024-01-15 |
| Banana | 2024-01-20 |
| Apple | 2024-02-05 |
| Banana | 2024-01-10 |
=MAXIFS($B$2:$B$5,$A$2:$A$5,"Apple")→0
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Ensure dates are stored as real date values, not text strings, in column B.
- 2In Excel 2019+ or Google Sheets, use =MAXIFS(date_range,criteria_range,criteria): =MAXIFS($B$2:$B$100,$A$2:$A$100,E2).
- 3For older Excel versions use the array formula =MAX(IF($A$2:$A$100=E2,$B$2:$B$100)) confirmed with Ctrl+Shift+Enter.
Tips
- Always format the result cell as Date (Ctrl+1 → Date). MAXIFS returns a date serial number that looks like a meaningless integer without formatting.
- For Excel 2016 or older that lacks MAXIFS, use =MAX(IF($A$2:$A$100=E2,$B$2:$B$100)) entered with Ctrl+Shift+Enter as an array formula. In Excel 365 it works without Ctrl+Shift+Enter.
- To find the EARLIEST date instead, swap MAXIFS for MINIFS with the same arguments.
- If your criteria is in a cell, reference it: =MAXIFS($B$2:$B$100,$A$2:$A$100,E2) — easier to audit and lets the formula update when E2 changes.
- For criteria that span a date range, MAXIFS accepts multiple criteria pairs: =MAXIFS($C$2:$C$100,$A$2:$A$100,"Apple",$B$2:$B$100,">="&DATE(2024,1,1)).
- When the lookup item does not exist, MAXIFS returns 0 (which formats as 1900-01-00). Wrap with IF: =IF(COUNTIF($A:$A,E2)=0,"Not found",MAXIFS(...)) to show a clean message.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I return a value from another column on the row with the most recent date?
Use INDEX/MATCH: =INDEX(C2:C100,MATCH(MAXIFS(B2:B100,A2:A100,E2),B2:B100,0)) retrieves the value in column C from the row with the latest date for the item in E2.
What if multiple rows share the most recent date?
MATCH returns the FIRST row that ties. To return all values on the latest date, use FILTER: =FILTER(C2:C100,(A2:A100=E2)*(B2:B100=MAXIFS(B2:B100,A2:A100,E2))).
How do I look up the most recent date without specifying an item?
Use plain MAX on the date column: =MAX($B$2:$B$100) returns the latest date across all items.
Why does MAXIFS return 0 / 1/0/1900?
No rows matched the criteria. Check the criteria value for trailing spaces (use TRIM), case mismatches, or text-vs-number issues. Wrap with IFERROR or COUNTIF to show a friendly message.
Does this work for date-time values (date + time stamps)?
Yes — MAXIFS treats date-time as a single decimal number, so it returns the latest exact moment. Format the result cell as date-time to see both parts.
How do I do this in Google Sheets?
MAXIFS works identically. Sheets users can also use SORT+FILTER: =FILTER(B:B,A:A=E2) wrapped in SORT(..., 1, FALSE) then INDEX to pull the top row.
More on Lookups (any direction)
See all →Reference
HLOOKUP
Look up a value in the top row of a table and return a value from a row below it — the horizontal cousin of VLOOKUP.
How-toHow to look up the last matching value
Use LOOKUP with a search trick or XLOOKUP with search mode -1 to find the last occurrence of a value in a list.
How-toHow to do a lookup with wildcards
Use VLOOKUP or MATCH with wildcard characters (* and ?) to perform partial-match lookups on text values.
How-toHow to do a case-sensitive lookup
Use INDEX with MATCH and the EXACT function to perform a lookup that distinguishes between uppercase and lowercase text.
How-toHow to look up a value and sum the matches
Use SUMIF or SUMIFS to sum all values that match a lookup criterion, returning the total of all matching rows rather than a single result.
How-toHow to find the closest matching value
Use INDEX and MATCH with ABS and MIN to find the value in a list that is numerically closest to a target number.