### 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?