Find lowest value within group

**heidenman** · 07-29-2009, 05:12 AM

I need to find the lowest value in a specific column when match from a "base cell" is apparent in another.

For example:

In column 1 I have groups.

a1 is apples
b1 is pears
c1 is bananas
d1 is apples
e1 is pears
f1 is bananas

In column 2 I have prices

a2 is 10,00
b2 is 15,00
c2 is 20,00
d2 is 12,00
e2 is 17,00
f2 is 22,00

In column 10 I have an input box where you enter fruit.
a10 is apples

I want the output to be the cheapest priced apple.

Anyone? Thanks!!

**Phil_V** · 07-29-2009, 05:47 AM

I think you might have column and row references muddled in your post, columns are 'lettered' and rows are numbered.

Anyway, here is the test data I used, (the titles are in A1 and B1 respectively):

Please Login or Register  to view this content.

E1 is the INPUT cell where you enter the name of fruit you are interested in.
E2 is the OUTPUT cell where the min price will be.

This code needs to go into the 'Sheet' area of the code.
Right click on the tab of the worksheet your data is on, and 'View Code'. Then paste the following into the editor.

Please Login or Register  to view this content.

**daddylonglegs** · 07-29-2009, 05:56 AM

You can use an "array formula" like this

=MIN(IF(A1:A10=C1,B1:B10))

confirmed with CTRL+SHIFT+ENTER

where C1 contains the fruit for which you want the lowest price