How to formulate Min Max Function with Criteria?

**geng** · 07-08-2010, 06:03 AM

I have 50 items, with 5 random different codes. I need to return the smallest value of each code. I tried combining IF, Vlookup and Min functions, but I just can't get it right.

I was thinking of sumproduct, but I don't know how to start.

Attach is my sample file, I have the data tab and the output tab.

Thank you in advance!

**Bob Phillips** · 07-08-2010, 08:33 AM

Try these array formulae

=INDEX(Data!A:A,MATCH(1,(MIN(IF(Data!$B$1:$B$50=$A4,Data!$C$1:$C$50))=Data!$C$1:$C$50)*(Data!$B$1:$B$50=$A4),0))

and

=INDEX(Data!C:C,MATCH(1,(MIN(IF(Data!$B$1:$B$50=$A4,Data!$C$1:$C$50))=Data!$C$1:$C$50)*(Data!$B$1:$B$50=$A4),0))

**geng** · 07-08-2010, 07:04 PM

Hi Bob, it returns #N/A, and when I tried evaluating it shows "false" in this part
IF(Data!$B$1:$B$50=$A4,Data!$C$1:$C$50))=Data!$C$1:$C$50)*(Data!$B$1:$B$50=$A4)

Thanks!

**geng** · 07-08-2010, 07:35 PM

hey Bob!
it's okay now, I just forgot to do the CTRL-SHIFT ENTER thing! Stupid me!

Anyway, Thank you very much again and again and again!!!

Mwahhhhhh!!!!