Using Min and if to produce a text response

[ajt8888]

This is probably very straightforward but its doing my head in, can someone help I have a worksheet which has two columns one with Names the other with value, all i need is a formula in column 3 that will return for each individual "lowest" for the lowest value attributed to that person and "ignore" to the rest of the values for that person. Many thanks in advance.

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl63, width: 64"][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl66, width: 64"]Name[/TD]
[TD="class: xl66, width: 64"]Value[/TD]
[/TR]
[TR]
[TD="class: xl65"]Harry[/TD]
[TD="class: xl65, align: right"]567[/TD]
[/TR]
[TR]
[TD="class: xl65"]Harry[/TD]
[TD="class: xl65, align: right"]568[/TD]
[/TR]
[TR]
[TD="class: xl65"]Harry[/TD]
[TD="class: xl65, align: right"]569[/TD]
[/TR]
[TR]
[TD="class: xl65"]Harry[/TD]
[TD="class: xl65, align: right"]570[/TD]
[/TR]
[TR]
[TD="class: xl65"]Harry[/TD]
[TD="class: xl65, align: right"]571[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dave[/TD]
[TD="class: xl65, align: right"]459[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dave[/TD]
[TD="class: xl65, align: right"]460[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dave[/TD]
[TD="class: xl65, align: right"]461[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dave[/TD]
[TD="class: xl65, align: right"]462[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dave[/TD]
[TD="class: xl65, align: right"]463[/TD]
[/TR]
[TR]
[TD="class: xl65"]Dave[/TD]
[TD="class: xl65, align: right"]464[/TD]
[/TR]
[TR]
[TD="class: xl65"]Pete[/TD]
[TD="class: xl65, align: right"]333[/TD]
[/TR]
[TR]
[TD="class: xl65"]Pete[/TD]
[TD="class: xl65, align: right"]331[/TD]
[/TR]
[TR]
[TD="class: xl65"]Pete[/TD]
[TD="class: xl65, align: right"]332[/TD]
[/TR]
[TR]
[TD="class: xl65"]Pete[/TD]
[TD="class: xl65, align: right"]333[/TD]
[/TR]
[TR]
[TD="class: xl65"]Pete[/TD]
[TD="class: xl65, align: right"]333[/TD]
[/TR]
[TR]
[TD="class: xl65"]Pete[/TD]
[TD="class: xl65, align: right"]338[/TD]
[/TR]
[TR]
[TD="class: xl65"]Steve[/TD]
[TD="class: xl65, align: right"]172[/TD]
[/TR]
[TR]
[TD="class: xl65"]Steve[/TD]
[TD="class: xl65, align: right"]173[/TD]
[/TR]
[TR]
[TD="class: xl65"]Steve[/TD]
[TD="class: xl65, align: right"]174[/TD]
[/TR]
[TR]
[TD="class: xl65"]Steve[/TD]
[TD="class: xl65, align: right"]175[/TD]
[/TR]
[TR]
[TD="class: xl65"]Steve[/TD]
[TD="class: xl65, align: right"]176[/TD]
[/TR]
[TR]
[TD="class: xl65"]Steve[/TD]
[TD="class: xl65, align: right"]177[/TD]
[/TR]
[TR]
[TD="class: xl65"]Steve[/TD]
[TD="class: xl65, align: right"]177[/TD]
[/TR]
[TR]
[TD="class: xl65"]Steve[/TD]
[TD="class: xl65, align: right"]177[/TD]
[/TR]
[TR]
[TD="class: xl65"]Steve[/TD]
[TD="class: xl65, align: right"]177[/TD]
[/TR]
[TR]
[TD="class: xl65"]Steve[/TD]
[TD="class: xl65, align: right"]177[/TD]
[/TR]
[TR]
[TD="class: xl65"]Steve[/TD]
[TD="class: xl65, align: right"]177[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl63, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

In cell C2, you can have following Array formula

=MIN(IF($A$2:$A$29=A2,$B$2:$B$29))

Instead of the Enter key ... use simultaneously the 3 keys : Control Shift Enter

Hope this will help

 

[ajt8888]

James thanks but that is not what was required, what I want is a formula that identifies the lowest number and puts the word lowest into cell c2 or if its not the lowest then ignore

Hello,

In cell C2, you can have following Array formula

=MIN(IF($A$2:$A$29=A2,$B$2:$B$29))

Instead of the Enter key ... use simultaneously the 3 keys : Control Shift Enter

Hope this will help

 

[James006]

Hello again,

Have a try with:

=IF(B2=MIN(IF($A$2:$A$29=A2,$B$2:$B$29)),"Lowest","")

HTH

 

[ajt8888]

Thanks James thats great

Hello again,

Have a try with:

=IF(B2=MIN(IF($A$2:$A$29=A2,$B$2:$B$29)),"Lowest","")

HTH

 

[James006]

You are welcome ...