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
- Format the result cell as a date (Ctrl+1 → Date) because MAXIFS returns a date serial number that looks like a large integer without formatting.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
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.
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.