Get minimum value based on criteria

[marlina]

[TABLE="width: 140"]
<colgroup><col width="70" span="2" style="width:53pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 70, align: right"]1[/TD]
[TD="class: xl64, width: 70, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl66, align: right"]9.2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl64, align: right"]8.3[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl66, align: right"]8.3[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl64, align: right"]8.8[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl66, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl64, align: right"]10.7[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]8[/TD]
[TD="class: xl66, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl64, align: right"]9.1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]10[/TD]
[TD="class: xl66, align: right"]10.3[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]11[/TD]
[TD="class: xl64, align: right"]9.9[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]12[/TD]
[TD="class: xl66, align: right"]8.5[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl64, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl66, align: right"]8.6[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl64, align: right"]9.1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl66, align: right"]8.3[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl64, align: right"]8.1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl66, align: right"]10.4[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl64, align: right"]9.1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]8[/TD]
[TD="class: xl66, align: right"]8.4[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl64, align: right"]8.3[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]10[/TD]
[TD="class: xl66, align: right"]9.7[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]11[/TD]
[TD="class: xl64, align: right"]9.7[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]12[/TD]
[TD="class: xl66, align: right"]9.5[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl64, align: right"]8.3[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl66, align: right"]9.1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl64, align: right"]8.5[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl66, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl64, align: right"]8.2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl66, align: right"]8.9[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl64, align: right"]11.5[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]8[/TD]
[TD="class: xl66, align: right"]8.3[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl64, align: right"]8.6[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]10[/TD]
[TD="class: xl66, align: right"]9.1
[/TD]
[/TR]
</tbody>[/TABLE]


Criteria = 1

={MIN(IF(A1:A34=D1,B1:B34),)}
not working because IF gives value of column B and 0 back and min just give 0

=AGGREGATE(5,4,INDEX(B1:B34,(A1:A34=D1)*ROW(A1:A34),))
not working because index just return row value of the first number in the array. . .
isn't index supposed to handle array operations ?

 

[Marcelo Branco]

Try

=AGGREGATE(15,6,B1:B34/(A1:A34=D1),1)

M.

 

[Marcelo Branco]

Or

=MIN(IF(A1:A34=D1,B1:B34))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[marlina]

Try

=AGGREGATE(15,6,B1:B34/(A1:A34=D1),1)

M.

thank you. it is working. another question. Index formulas can handle array operations, why my formula isn't working.

 

[Marcelo Branco]

You are welcome.

About your question
INDEX is not necessary in this case.

M.