Find The Lowest Corresponding Value For A Repeated Value

**rovaniemi** · 07-03-2013, 11:59 AM

Hi everyone

I am new to this forum and very bad in excel. I have hit a roadblock right now. I have attached an excel file.

My issue is this:

I want to find the lowest value associated with a each codes (212, 223, 230, 242) corresponding to its value in the C column.

I have tried using this formula - =IF(COUNTIF($B$2:B2,B2)>1,"",MIN(IF(($B$2:$B$17=B2 )*($C$2:$C$17<>0),$C$2:$C$17)))

When using this formula, the value associated with Column B is changing as the cell changes, but the value of Column C doesn't change accordingly, when the equation is applied in the Column D. The result remains the same for the entire D column.

The resultant value of 212 is 3. The values for 223, 230, 242 should be 1, 3, 5 respectively. I would be very much obliged if someone can derive one formula with which I can get the required results.

The value of the C column should change (finding the lowest value)

Thank you all in advance for your replies and efforts.

**rollis13** · 07-03-2013, 01:44 PM

Cross-post: Excelguru.ca

**Rick Rothstein** · 07-03-2013, 02:04 PM

Give this array-entered** formula a try...

=IF(COUNTIF($B$2:B2,B2)>1,"",MIN(IF(($B$2:$B$17=B2 ),$A$2:$A$17)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

IMPORTANT NOTE: Turn Automatic Calculations back on... for some reason you have it set to Manual right now.

**rovaniemi** · 07-03-2013, 02:04 PM

I don't understand. Did I make any mistake?

**rovaniemi** · 07-03-2013, 02:15 PM

Thank you Rick. But when I entered the formula the result it gave me was 41085 rather than 1,3,5 respectively. Yes I turned on automatic calculations.

**Rick Rothstein** · 07-03-2013, 02:27 PM

Originally Posted by rovaniemi

Thank you Rick. But when I entered the formula the result it gave me was 41085 rather than 1,3,5 respectively. Yes I turned on automatic calculations.

Sorry, 41085 is the serial number for a date... I mistakenly thought you wanted the earliest date, not the minimum value in Column C. Here is the array-entered** formula modified to return values from Column C...

=IF(COUNTIF($B$2:B2,B2)>1,"",MIN(IF(($B$2:$B$17=B2 ),$C$2:$C$17)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

**rovaniemi** · 07-03-2013, 02:32 PM

Rick thanks again. Using the above formula gives 0 as result.I don't know If I have been explaining things correctly. When I said minimum value what I meant is not zero.

We don't need zero here. The minimum value for 212 is 3. Likewise the minimum value for 223, 230, 242 should be 1, 3, 5 respectively. That is the formula I am looking for.

Thank you so much for the reply and patience.

I have attached image and excel file of how the answer should be.

**Admin** · 07-03-2013, 03:24 PM

Hi

Tweak Rick's formula like this one.

=IF(COUNTIF($B$2:B2,B2)>1,"",MIN(IF($B$2:$B$17=B2, IF($C$2:$C$17<>0,$C$2:$C$17))))

Entered as an array formula

**rovaniemi** · 07-03-2013, 05:59 PM

A million thanks to Admin and Rick. You guys are simply brilliant. The formula worked like charm. Thank you so so so much for your help. May God bless you all.