Minimum-Date per Criteria using Excel Formula

Question

I realise this is trivial to achieve with a PivotTable: but can I please get help returning the minimum 'Date' for each 'Name' in table below using (assumedly) a min/if array formula?

Date       Name
1/01/2015  john
1/01/2016  john
1/01/2017  john
1/01/2018  bob
1/01/2019  bob
1/01/2020  cow
1/01/2021  cow

Expected result (in column 2) in second table below: i.e. for each static entry in 'Name (unique)', I'm wanting a formula for column 'Date (min)' that will give me the minimum date next to the name from table above, as shown:

Name (unique)  Date (min)
john           1/01/2015
bob            1/01/2018
cow            1/01/2020

I don't need a method to get the unique list of names: that will be set. Just wanting the formula for 'Date (min)' please. I realise sorting by date and using Match or Vlookup would return same result, but not possible with my 'actual' data

Forty3 Forty3 · Accepted Answer · 2019-01-30T05:32:58

Thanks for the opportunity to learn a bit more about Excel .. specifically Array Formulas.

Given the following:

     A          B
     ---------  -----------
1    Date       Name
2    1/01/2015  john
3    1/01/2016  john
4    1/01/2017  john
5    1/01/2018  bob
6    1/01/2019  bob
7    1/01/2020  cow
8    1/01/2021  cow

Then use the Array Formula syntax to find the minimum value in col A for the unique value in col B:

    F              G
    -------------  ----------------------------------------------
1    Name (unique)  Date (min)
2    john           =MIN(IF(B$2:B$8=F2, A$2:A$8, DATE(9999,12,31)))
3    bob            =MIN(IF(B$2:B$8=F3, A$2:A$8, DATE(9999,12,31)))
4    cow            =MIN(IF(B$2:B$8=F4, A$2:A$8, DATE(9999,12,31)))

The key is to invoke the Array Formula syntax by pressing Ctrl + Shift + Enter when you have finished typing in the formula. The result, in the formula bar will look like:

{=MIN(IF(B$2:B$8=F2, A$2:A$8,DATE(9999,12,31)))}

Note the surrounding {...}.

Many thanks to this article.

Minimum-Date per Criteria using Excel Formula

1 Answers