Formula needed

[xbyrne]

Hi, I've tried loads of different formulas but can't get one to work.

What I'm looking for is a formula that says something like:

If a1=1 then apple, but if it doesn't and b1=1 then banana, but if they both don't and c1=1 then pear.

Any ideas?

 

[arunsjain]

Try this one:

=IF(AND(A1=1,B1="",C1=""),"Apple",IF(AND(A1="",B1=1,C1=""),"Banana",IF(AND(A1="",B1="",C1=1),"Pear",IF(AND(A1=1,B1=1,C1=1),"Input Error",IF(AND(A1=1,B1=1,C1=""),"Input Error",IF(AND(A1=1,B1="",C1=1),"Input Error",IF(AND(A1="",B1=1,C1=1),"Input Error","")))))))

Make sure 1 should be either in A1 or B1 or C1 then only you will have Apple, Banana or Pear respectively. Otherwise formula will show input error.

Hope it will help.

 

[FormR]

Hi, welcome to the forum!

Here is another option.

=CHOOSE(MATCH(1,A1:C1,0),"Apple","Banana","Pear")

 

[inactiveuserps07]

@xbyrne:

=IF(A1=1,"apple",IF(B1=1,"banana",IF(C1=1,"pear","no match")))

Or adapting FormR's approach above, to control for the even that none of the cells equals 1:

[COLOR=#333333][FONT=Verdana]=IFERROR(CHOOSE(MATCH(1,A1:C1,0),"Apple","Banana","Pear"),"No Match")[/FONT][/COLOR]

 

[xbyrne]

Thanks for the replies. I can't seem to get one to work though.

I think the problem is that the boxes with the numbers on them can contain different numbers and it's only when they have "1" in them that the other cell puts the message up

 

[inactiveuserps07]

@xbyrne, it shouldn't matter what number you have in the cells using this formula, which I provided above (pasted again here for your convenience):

[COLOR=#333333]=IF(A1=1,"apple",IF(B1=1,"banana",IF(C1=1,"pear","no match")))[/COLOR]

If that doesn't work, it's possible that your 1 is not a numeric value, but rather is text, in which case just put quotation marks around each 1 in the formula above.

 

[xbyrne]

Thank you @ErikTyler, that's exactly what it was. I only used the 1 to try and simplify explaining it.

You are a star

 

[inactiveuserps07]

@xbyrne, glad I was able to point you in the right direction. It's always a bit tricky using dummy data to get at a real solution; but in this case, all's well that ends well.