Earliest date

New Contributor


I would like to get the earliest date from a set of data where per each person I've got more than a date.


I´m struggling to get the unique name and the earliest date as shown above.

Any suggestion?



13 Replies


{=MIN(IF($B$1:$B$10 = "John", $A$1:$A$10))}

array formula
Ctrl + Shift + Enter to complete the formula


Added example file.


Hope I could help you with these information.



I know I don't know anything (Socrates)


In D1:


In E1:


Format column E as a date.

Hi @jlgomes 


With a Pivot Table. Drop Dates to the Values area, aggregation = Min:


Thanks @NikolinoDE. Allways returns 00-01-1900 

Thanks, allways returns 00-01-1900 
array formula - got o the formula and press at the same time " Ctrl + Shift + Enter " to complete, then you will see the right date


As usual, I would go with @Hans Vogelaar's solution.

The first step might be "If you are using any spreadsheet software other than Excel 365, bin it and start fresh!" 

Packaging the solution into a single formula, you might have

= LET(
  distinctNames, UNIQUE(name),
  ealiestDates,  MINIFS(date,name,distinctNames),

If your focus were on the further use of the earliest dates as opposed to merely displaying the result, you could define a Lambda function

= EarliestDateλ("John")
= LAMBDA(n, MINIFS(date, name, n));



Are you sure you used the correct ranges?

Pivot tables seem me to be the easiest way to go. If yo see zeros, there might be empty rows, or rows with only names.
Kind regards

To add to this as a demo of modern Excel, the Lambda function could be reused to allow the person with the latest start date to be identified.

= LET(
  latestStart,  MAX(EarliestDateλ(UNIQUE(name))),
  FILTER(Input, date=latestStart)


@Peter Bartholomew 

As variant

   s, SORT( Input, 2, 1),
   u, UNIQUE( Input[Name] ),
   HSTACK( u, XLOOKUP( u, CHOOSECOLS( s, 1 ), CHOOSECOLS( s, 2) ) ) )

and descending sort for the latest date. For the latest startdate

   s, SORT( Input, 2, 1),
   u, UNIQUE( Input[Name] ),
   m, MAX( XMATCH( u, CHOOSECOLS( s, 1) ) ),
   CHOOSEROWS( s, m ) )


@Sergei Baklan 

I would be happy to go with those!  It seems we have a surfeit of choice when it comes to selecting the best methods at the moment.  I suspect I tend to resort to thunks too readily and I still have to get to the bottom of the array shaping possibilities offered by TOROW/COL, WRAPROWS/COLS, SORT!