smallest out of all index-match results

[Raykage]

Hi guys,

I have 2 columns (part number A1:A100 and date B1:B100).
On second sheet I have just a column with part numbers F1:F10.

I want to have a second column G1:G10 in which each cell checks the part number in the F column,
does an index match on the A and B columns to get the earliest date of that part number.
It would have to be a formula which can provide all index match results and get the earliest date by =min or =small
or something like that. Unfortunately I've got no idea if it's possible or if there is any other way, since index-match only give the first result.

Any ideas?

 

[DRSteele]

Welcome to the forum.

If you're using the most up-to-date version of Excel, the easiest solution in MINIFS. There are other solutions for old Excel (prior to 2013 version).

I included error-checking to account for Part Numbers you might look up that are not in the Data in Columns A and B, in which case the formulas report blanks ("").

	A	B	C	D	E	F
1	Part	Date		Part	Earliest (MINIFS)	Earliest (AGGREGATE)
2	1	7/18/2016		1	7/18/2016	7/18/2016
3	1	7/19/2016		2
4	3	7/20/2016		3	7/20/2016	7/20/2016
5	5	7/21/2016		4
6	6	7/22/2016		5	7/21/2016	7/21/2016
7	6	7/23/2016		6	7/22/2016	7/22/2016
8	8	7/24/2016		7
9	8	7/25/2016		8	7/24/2016	7/24/2016
10	8	7/26/2016		9	7/27/2016	7/27/2016
11	9	7/27/2016

<tbody>
</tbody>

Sheet25​

Worksheet Formulas Cell Formula E2 =IF(MINIFS($B$2:$B$11,$A$2:$A$11,D2)=0,"",MINIFS($B$2:$B$11,$A$2:$A$11,D2)) F2 =IFERROR(AGGREGATE(15,6,1/($A$2:$A$11=D2)*$B$2:$B$11,1),"") <tbody> </tbody>

<tbody>
</tbody>

 

[Raykage]

Thank you DRSteele!! That's exactly what I was looking for (I'm surprised that it's so easy, I didn't know MINIFS)
Thanks for the effort!!

 

[DRSteele]

You're welcome. I'm glad it worked out for you. Don't forget about MAXIFS and AVERAGEIFS - they're as useful as MINIFS.

If you like Excel, keep coming back to this forum for answers. And check out this channel on youtube - it's very informative and it has billions and billions of videos on how to drive Excel. https://www.youtube.com/user/ExcelIsFun