Formula to return value in another column based on Smallest value

[DerekWooley]

HI, I have seen there are ways to return the smallest value with certain criteria using the SMALL(if(Criteria Array: Criteria Array=Criteria, Value Range:Value Range),1).

I want to know if there is a way to identify the Smallest value and return a value in another column based on criteria. Below is my data. I want to return the Machine # where the smallest date is for each Item # in Column E. My Current Small formula is returning the date in Column D correctly.

[TABLE="width: 701"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Column1[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[TD]Col E[/TD]
[/TR]
[TR]
[TD]Items[/TD]
[TD]Date[/TD]
[TD] [/TD]
[TD]Machine[/TD]
[TD]Return smallest date for Item #[/TD]
[TD]Return Machine where smallest date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8/15/2019[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8/18/2019[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8/23/2019[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8/28/2019[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]8/31/2019[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]9/8/2019[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]9/13/2019[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]9/19/2019[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]9/25/2019[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Queue[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]12/31/1999[/TD]
[TD] [/TD]
[TD]B[/TD]
[TD]9/13/2019[/TD]
[TD]Wanted formula:Should Return A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12/31/1999[/TD]
[TD] [/TD]
[TD]B[/TD]
[TD]8/23/2019[/TD]
[TD]Wanted formula:Should Return A[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]12/31/1999[/TD]
[TD] [/TD]
[TD]B[/TD]
[TD]9/8/2019[/TD]
[TD]Wanted formula:Should Return A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12/31/1999[/TD]
[TD] [/TD]
[TD]B[/TD]
[TD]8/18/2019[/TD]
[TD]Wanted formula:Should Return A[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]12/31/1999[/TD]
[TD] [/TD]
[TD]B[/TD]
[TD]8/31/2019[/TD]
[TD]Wanted formula:Should Return A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12/31/1999[/TD]
[TD] [/TD]
[TD]B[/TD]
[TD]8/15/2019[/TD]
[TD]Wanted formula:Should Return A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12/31/1999[/TD]
[TD] [/TD]
[TD]B[/TD]
[TD]8/28/2019[/TD]
[TD]Wanted formula:Should Return A[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]

 

[Aladin Akyurek]

It is not clear how input and output are set out.


Care to post them sequentially, not to each other?

 

[DerekWooley]

Sure, see below. The second half under where it says Queue in Column E is the output where I want the formulas to be. Columns A B and C above queue are the input where the formulas should read off. As an example, the first line under the queue is for item 7. I want the formula for that row under Column E to look at Column A above and match to item 7, then look at Column B and find the smallest date, which is 8/28/2019, and return the machine letter "C" next to it. There are two instances where item 7 shows up in the Input section so I want it to pick up the smaller of the 2.

I can't use vlookup because there are other situations in my data where the first instance is not the smallest.


[TABLE="width: 701"]
<tbody>[TR]
[TD]Input[/TD]
[TD]Input[/TD]
[TD][/TD]
[TD]Input[/TD]
[TD]Output[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Column1[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[TD]Col E[/TD]
[/TR]
[TR]
[TD]Items[/TD]
[TD]Date[/TD]
[TD] [/TD]
[TD]Machine[/TD]
[TD]Return smallest date for Item #[/TD]
[TD]Return Machine where smallest date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8/15/2019[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8/18/2019[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8/23/2019[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]8/28/2019[/TD]
[TD] [/TD]
[TD]C[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]8/31/2019[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]9/8/2019[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]9/13/2019[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]9/19/2019[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]9/25/2019[/TD]
[TD] [/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]

[/TD]
[TD]Queue[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]12/31/1999[/TD]
[TD] [/TD]
[TD]B[/TD]
[TD]8/28/2019[/TD]
[TD]Output formula:Should Return C[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12/31/1999[/TD]
[TD] [/TD]
[TD]B[/TD]
[TD]8/23/2019[/TD]
[TD]Output formula:Should Return A[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]12/31/1999[/TD]
[TD] [/TD]
[TD]B[/TD]
[TD]9/8/2019[/TD]
[TD]Output formula:Should Return A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12/31/1999[/TD]
[TD] [/TD]
[TD]B[/TD]
[TD]8/18/2019[/TD]
[TD]Output formula:Should Return A[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]12/31/1999[/TD]
[TD] [/TD]
[TD]B[/TD]
[TD]8/31/2019[/TD]
[TD]Output formula:Should Return A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12/31/1999[/TD]
[TD] [/TD]
[TD]B[/TD]
[TD]8/15/2019[/TD]
[TD]Output formula:Should Return A[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col></colgroup>[/TABLE]

 

[Aladin Akyurek]

The dates appear in chronological order...

A3:F21 contains the input and the output.

In E16 control+shift+enter, not just enter, and copy down:

=MIN(IF($A$4:$A$12=A16,$B$4:$B$12))

In F16 just enter and copy down:

=INDEX($D$4:$D$12,MATCH(E16,$B$4:$B$12,0))

Note. If MINIFS is available on your system, the first formula can be made a tad faster.

 

[DerekWooley]

This works perfectly. Thank you!

 

[Aladin Akyurek]

This works perfectly. Thank you!

You are wellcome.