Can I combine MIN with an Index Match array?

[bradams]

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.

	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!

 

[Eric W]

Try:

	A	B	C	D	E
1	Name	Assignment Year	Name	Earliest Assignment
2	John	2019	John	2017	2017
3	Bill	2015
4	John	2017
5	Nancy	2016
6	John	2018

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula E2 =MINIFS(B:B,A:A,C2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D2 {=MIN(IF($A$2:$A$6=C2,$B$2:$B$6))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The MINIFS is available in Excel 365.

 

[bradams]

Thanks. I was making it way more complicated than necessary.

 

[Eric W]

You're welcome. Another set of eyes can really help sometimes.