Get minimum value based on criteria

[marlina]

1	9
2	9.2
3	8.3
4	8.3
5	8.8
6	10
7	10.7
8	8
9	9.1
10	10.3
11	9.9
12	8.5
1	10
2	8.6
3	9.1
4	8.3
5	8.1
6	10.4
7	9.1
8	8.4
9	8.3
10	9.7
11	9.7
12	9.5
1	8.3
2	9.1
3	8.5
4	8
5	8.2
6	8.9
7	11.5
8	8.3
9	8.6
10	9.1

<colgroup><col width="70" span="2" style="width:53pt"> </colgroup><tbody>
</tbody>

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.