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
Part	Date	Part	Earliest (MINIFS)	Earliest (AGGREGATE)

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/18/2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: #E2EFDA, align: right"]7/18/2016[/TD]
[TD="bgcolor: #E2EFDA, align: right"]7/18/2016[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7/19/2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7/20/2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #E2EFDA, align: right"]7/20/2016[/TD]
[TD="bgcolor: #E2EFDA, align: right"]7/20/2016[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7/21/2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7/22/2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="bgcolor: #E2EFDA, align: right"]7/21/2016[/TD]
[TD="bgcolor: #E2EFDA, align: right"]7/21/2016[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7/23/2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="bgcolor: #E2EFDA, align: right"]7/22/2016[/TD]
[TD="bgcolor: #E2EFDA, align: right"]7/22/2016[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7/24/2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7/25/2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="bgcolor: #E2EFDA, align: right"]7/24/2016[/TD]
[TD="bgcolor: #E2EFDA, align: right"]7/24/2016[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]7/26/2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="bgcolor: #E2EFDA, align: right"]7/27/2016[/TD]
[TD="bgcolor: #E2EFDA, align: right"]7/27/2016[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]7/27/2016[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet25​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IF(MINIFS($B$2:$B$11,$A$2:$A$11,D2)=0,"",MINIFS($B$2:$B$11,$A$2:$A$11,D2))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=IFERROR(AGGREGATE(15,6,1/($A$2:$A$11=D2)*$B$2:$B$11,1),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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