"min if".....?

[fiur]

Hi I got this problem....
in cell c5.....c100 I got an text value, in cell i5 I got 1 text value (who shows up in c5 to c100....
in cell d5..d100 I got an number value.....

I wanna find the lowest "number value" if the text is the same (if i5=c5..c100 then min d5..d100)

I hope you understand my problem...

[deadlyduck]

Are the text values in c5:c100 all unique ie the same text doesn't appear 2 or more times in different cells, so, for example, you don't have "hello" in both cell c5 and c27?

If the values are unique, you could use the following formula:

=VLOOKUP(I5,C5:D100,2,TRUE)

However, this won't give the right answer if the text in c5:c100 contains repetitions.

[Dav]

As column C is not sorted, or at least we have not been told it is sorted you need

=VLOOKUP(I5,C5:D100,2,False)



Regards

Dav

[fiur]

yes the text in cell c5..c100 can repeats.....

here is an example.....

C5= hello D5= 1
C6= goodbye D6=4
C7= hello D7=3

In cell K5 I wanna find the min. value of D5..D100 if I5= c5..C100

(an yes all vaules in C5..C100 will repeats....)

[daddylonglegs]

Try this formula. Needs to be confirmed with CTRL+SHIFT+ENTER

=MIN(IF(C$5:C$100=I5,D$5:D$100))

copied down

[fiur]

Now I think we are close... (no errors in the formula now)
but the result is all wrong.....
I got several cells that contains a value below zero....

but in all my calculations it returns with zero all the time...?

lets say several result should be like -11, -14, -9 and so on....
but the lowest I get is zero....??

[daddylonglegs]

Should show the lowest value regardless...

Are you confirming the formula with CTRL+SHIFT+ENTER?

To do this select cell with formula, press F2, hold down CTRL and SHIFT keys and press ENTER. Curly braces like { and } should appear around the formula in the formula bar.

Are they true negative values? [rather than text, for instance]. If you have a "negative value" in D10 try using the formula

=ISNUMBER(D10)

this should give TRUE

I've attached an example

[fiur]

well my fault... I got cells hand I mergded.... so when I change from i to h in the formula everythings worked fine...
thanx alot for all help!