Find the Smallest Value in an Array based on a specific Item

Earlier today a co worker presented me with an Excel problem that he could not figure out. He wanted to find the lowest value in a table. Here is a short sample of the table he was looking at.

	A	B	C
1	ID	Product	Sale Amount
2	10001	Hammer	15.50
3	10002	Saw	7.00
4	10003	Square	11.25
5	10004	Bracket	13.00
6	10005	Hammer	11.00
7	10006	Saw	12.00
8	10007	Square	10.75
9	10008	Saw	15.00
10	10009	Hammer	12.00
11	10010	Saw	13.50
12	10011	Square	6.40
13	10012	Bracket	13.00

So I thought to myself, easy I can just do a =Min formula on column C    =MIN(C:C) and return 6.40

However then he said, he wanted the minimum value of Saws in the table. In the above example that would be 7, not 6.40.

HMMMMM. No problem, I can use the =small function.

SMALL(array,k)

Now small returns the k-th smallest value in a data set. For example, the fifth smallest number.

Since I want the smallest number k = 1. If I wanted the second smallest number I would use 2.

So I wrap the small function around an IF statement.

=SMALL(IF(B2:B13="Saw",C2:C13,""),1)

NOW TO MAKE THIS WORK, TURN THE FORMULA INTO AN ARRAY

While editing the formula, press CTRL+SHIFT+ENTER

The result is 7 since the lowest sale amount for the product SAW is 7.

If he wanted the largest I could replace =SMALL with =LARGE.

Now he could use a cell reference to pull his value. =SMALL(IF(B2:B13=H1,C2:C13,""),1)

In this instance the value in cell H1 would be looked up in the formula. Now I added the cell reference after my original solution and received errors for some of my values in the list. To correct this, I needed to recalculate my formula as an array (While editing the formula, press CTRL+SHIFT+ENTER) after setting the cell reference value.

I like this solution since it is simple. His work around was to bring the table into MS Access and then create two queries to get the answer.

He walked away happy with new knowledge and I once again sat back basking in the glow of being the Excel go to guy.

So how would you have solved this Excel question?