How to lookup latest or earliest date in Excel?
If you have a list of dates in a worksheet, and you would like to lookup the latest or oldest date based on a specific value as following screenshot shown:
Do you have any ideas to deal with this problem in Excel?
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
To get the relative oldest or newest date of the specific value, you can apply the following formulas to solve it.
To get the oldest date, please input this formula: =MIN(IF(A2:A16=E1,B2:B16)) into a specified cell, and then press Ctrl + Shift + Enter keys together to return the correct answer, see screenshot:
To get the newest date, please enter this formula: =MAX(IF(A2:A16=E1,B2:B16)) into your specified cell, and remember to press Ctrl + Shift + Enter keys together to return the result, see screenshot:
1. In the above formulas:
- A2:A16 is the column that you want to look up in;
- E1 is the lookup value;
- B2:B16 is the column which the result will be returned.
2. If you get a number result after pressing the Ctrl + Shift + Enter keys, you just need to convert the number to date formatting. Select the number cell and right click to choose Format Cells, then in the Format Cells dialog box, click Date under Number tab, and choose one date format from the right Type list box.