Tom’s Tutorials For Excel: Lookup Oldest and Newest Date

Tom’s Tutorials For Excel: Lookup Oldest and Newest Date

The picture shows one way of obtaining the oldest and newest dates in column B that correspond to the person’s name in column A that is in cell D2 as a lookup criteria. Because dates are numbers, you can use the MIN function for the oldest date, and the MAX function for the newest date.

The formula in cell E2 for the oldest date is =MIN(IF(A2:A26=D2,B2:B26)).
The formula in cell F2 for the newest date is =MAX(IF(A2:A26=D2,B2:B26)).

Array formulas are committed to a cell by pressing Ctrl+Shift+Enter, not just Enter.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
10 comments on “Tom’s Tutorials For Excel: Lookup Oldest and Newest Date
  1. sumit majumder says:

    I need a details in excel that if i put an unique code than it will retrieve last date of visit excluding the current date & if unique code row is blank than return 0

    A B
    Reg. No Date
    1000 17-Jan-18
    1000 17-Jun-18
    And if i put 1000 in A4 than result will come in C4 is 17-Jun-18

  2. sujit says:

    how to find second min date and max date up to n …. numbers date

  3. sujit says:

    Hi Tom

    i am asking about upper example of tome

    tome 1st oldest date and newest date find that is (feb17,2011 and july09,2011) similar what about tome 2nd oldest date and newest date and which formula use to fined 3rd, 4th…..n number of Oldest and newest date to find.

  4. Bryan says:

    Thank you! Just what I was looking for.

  5. Melissa says:

    Hello, could you tell me what the formula is to look for the minimum date in a range but that particular date has to be after a certain date?

Leave a Reply

Your email address will not be published. Required fields are marked *