Recently new to excel and appreciate this forum very much. What I'm failing to do is find the most recent value compared to a date. So in the example below, I have a baseball schedule with both home and away. I want to be able to input a formula so it will populate a team's most recent home game date next to their name in the away column. If that makes any sense. Say in the example below, on 5/5/2024, I would like to know when the Reds last home date was. Thank you for any help you can provide.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Finding Most Recent Date from Specific Date
- Thread starter Cartay81
- Start date
Cubist
Well-known Member
- Joined
- Oct 5, 2023
- Messages
- 1,803
- Office Version
- 365
- Platform
- Windows
- MacOS
Try:
| Book1 | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Date | Home | Away | Last Home Game | ||
| 2 | 5/1/24 | Reds | Brewers | 5/6/24 | ||
| 3 | 5/1/24 | Giants | Padres | 5/2/24 | ||
| 4 | 5/1/24 | Cubs | Cardinals | 5/5/24 | ||
| 5 | 5/2/24 | Reds | Cubs | 5/6/24 | ||
| 6 | 5/2/24 | Giants | Brewers | 5/2/24 | ||
| 7 | 5/2/24 | Cardinals | Padres | 5/6/24 | ||
| 8 | 5/3/24 | Cubs | Cardinals | 5/5/24 | ||
| 9 | 5/3/24 | Cubs | Reds | 5/5/24 | ||
| 10 | 5/3/24 | Cardinals | Brewers | 5/6/24 | ||
| 11 | 5/4/24 | Padres | Giants | 5/6/24 | ||
| 12 | 5/4/24 | Cubs | Reds | 5/5/24 | ||
| 13 | 5/4/24 | Cardinals | Brewers | 5/6/24 | ||
| 14 | 5/5/24 | Cubs | Reds | 5/5/24 | ||
| 15 | 5/5/24 | Cardinals | Giants | 5/6/24 | ||
| 16 | 5/5/24 | Padres | Brewers | 5/6/24 | ||
| 17 | 5/6/24 | Reds | Brewers | 5/6/24 | ||
| 18 | 5/6/24 | Cardinals | Cubs | 5/6/24 | ||
| 19 | 5/6/24 | Padres | Giants | 5/6/24 | ||
Sheet6 | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2:D19 | D2 | =MAXIFS($A$2:$A$19,$B$2:$B$19,B2) |
Upvote
0
SanjayGMusafir
Well-known Member
- Joined
- Sep 7, 2018
- Messages
- 1,503
- Office Version
- 2021
- Platform
- MacOS
Check this and revert -
Excel Formula:
=LOOKUP(2,1/(C:C=C2),B:B)
Upvote
0
Similar threads
- Question
