Return result based on the lowest value, but higher than 0

[cowproduct]

Hello,

I have 3 columns, let's call them "Cat", "Dog" and "Rat". For each column I have values, and the base value is 0.

I'm trying to get a formula to show me "Cat", "Dog" or "Rat" based on the lowest value, but higher than 0.

The formula I have so far is:
=IF(AND(A5<B5,A5<C5),"Cat",IF(AND(B5<A5,B5<C5),"Dog",IF(AND(C5<A5,C5<B5),"Rat","No")))

This keeps returning anything that is 0. And the "No" would indicate that it is 0 of each. I have tried a few other variations of this too, but I'm not getting anywhere fast.

Also, What if Cat and Dog are both the same value. Is there a way I can have it return "Cat/Dog" or something?

I would be forever grateful for some help on this.

[dilipandey]

Hi Cowproduct,

Try using below formula to return either cat/dog/rat :-

=IF(MIN(A5,B5,C5)=A5,"Cat",IF(MIN(A5,B5,C5)=B5,"Dog","Rat"))

Regards,
DILIPandey

<click on below 'star' if this helps>

[cowproduct]

Unfortunately no, I just tried this and it also reverts to the 0.
If I say 2 dog, 1 cat, 0 rat, it should give me cat as the result. But it gives me rat again...

[dilipandey]

Oopss.. I got it..
Suggest you to upload a sample file with multiple scenarios you have.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[cowproduct]

Ok, I've attached it with some sums and your formula, as well as what the outcome should be.

If it needs to be across 3 columns with conditional formatting, that's fine too...

[vlady]

It's long maybe someone can help shorten this up.

Please Login or Register  to view this content.

array formula - ctrl + shift + enter

[cowproduct]

Brilliant! Thanks for that. Works a treat.
Only issue is the unsightly spaces. But I will just paste the values and replace spaces. Thank you!

[zbor]

How about this:

=INDEX($A$4:$C$4,MATCH(LARGE($A5:$C5,COUNTIF($A5:$C5,">0")),$A5:$C5,0))

[zbor]

Also, I've changed your title this time but next time I'll close a thread.

Why you wonder? Check the Rule number 1