You can use the following formula in Excel to lookup a specific value and then return the max date (i.e. most recent date) associated with the value:
This particular formula finds the max date in the range C2:C13 where the value in the range A2:A13 is equal to the value in cell F1.
The following example shows how to use this formula in practice.
Example: Lookup Value and Find Max Date in Excel
Suppose we have the following dataset in Excel that contains information about when basketball players joined specific teams:
Suppose we would like to look up the value “Mavs” in the Team column and then return the max date associated with this team.
We can specify the Mavericks team name in cell F1 and then type the following formula into cell F2 to find the max date:
The following screenshot shows how to use this formula in practice:
By default, Excel displays the date in a numeric format.
To convert this to a recognizable date format, select cell F2 and then click the Number Format dropdown menu on the Home tab and then click Short Date:
The date will now be formatted as 4/13/2009, which represents the earliest date in the Join Date column where the value in the corresponding cell of the Team column is equal to Mavs:
Note that if we change the team name in cell F1 then the formula will automatically update to find the max date associated with the new team name.
For example, suppose we change the team name to Rockets:
The formula now returns 6/5/2017, which is the max date (i.e. most recent date) associated with the Rockets.
The following tutorials explain how to perform other common tasks in Excel: