My data set has a list of people with an assignment date. Each person can have multiple assignment dates. I need to find the earliest assignment date for each person. I am trying to solve this by first doing an index match array formula to identify all the assignment dates and then applying a MIN function to those results. It's not working as it is returning the first date it finds. For this example I'll just use assignment year.
<tbody>
</tbody>
This is the closest I've come for the formula in D1 but, as I said, it's returning the first year matching John (2019), not the earliest year (2017).
{MIN(Index(B1:B5,MATCH(C1,A1:A5,0),1))}
As a one-off I could sort by assignment date but this is an ongoing report so I'm trying to automate this as much as possible. I cannot force the database to return the data pre-sorted and, honestly, cannot figure out how the data is being sorted.
Thanks in advance!
A | B | C | D | |
Name | Assignment Year | Name | Earliest Assignment | |
1 | John | 2019 | John | < Formula > |
2 | Bill | 2015 | ||
3 | John | 2017 | ||
4 | Nancy | 2016 | ||
5 | John | 2018 |
<tbody>
</tbody>
This is the closest I've come for the formula in D1 but, as I said, it's returning the first year matching John (2019), not the earliest year (2017).
{MIN(Index(B1:B5,MATCH(C1,A1:A5,0),1))}
As a one-off I could sort by assignment date but this is an ongoing report so I'm trying to automate this as much as possible. I cannot force the database to return the data pre-sorted and, honestly, cannot figure out how the data is being sorted.
Thanks in advance!
Last edited: