IF with 3 variables

[TRS]

Hi guys,

I think it is an easy problem for you.
Please find below the cells. I want to create a formula in D that tells me the type of data we are working on based on value in column a,b and c.
The logic for d2 should be
"if a2 is Y and b2 and c2 are N then give me apple" and
"if b2 is Y and a2 and c2 are N then give me banana" and
"if c2 is Y and b2 and a2 are N then give me pear"

there is can be only a "Y" per raw.

a b c d
1 Apple Banana Pear Type
2 Y N N Apple
3 N Y N Banana
4 N N Y Pear

Thanks in advance for your help.
Regards,

[TRS]

I attached a printscreen. It is clearer.

[Domski]

Maybe:

=IF(COUNTIF(A2:C2,"Y")>1,"Too Many Y's",IF(A2="Y","apple",IF(B2="Y","banana",IF(C2="Y","pear",""))))

Dom

[NBVC]

Try:

=INDEX({"Pear","Banana","Apple"},MATCH(A2&B2&C2,{"NNY","NYN","YNN"},0))

[Ron Coderre]

If you are only interested in the position of the "Y",
here are some options:

Please Login or Register  to view this content.

or...for more flexibility, read the column headings based on the matched "Y":

Please Login or Register  to view this content.

Does that help?

[scottylad2]

IF(AND(A2="y",B2="n",C2="n"),"apple",IF(AND(B2="y",A2="n",C2="n"),"banana",IF(AND(C2="y",B2="n",A2="n"),"pears","")))

think that covers all scenarios, including a non mentioned combination. ie 2 y's anywhere will display as blank

[TRS]

Thank you all guys!

I used =INDEX({"Pear","Banana","Apple"},MATCH(A2&B2&C2,{"NNY","NYN","YNN"},0)) as it was the first one working

instead =IF(COUNTIF(A2:C2,"Y")>1,"Too Many Y's",IF(A2="Y","apple",IF(B2="Y","banana",IF(C2="Y","pear","")))) did not work! maybe I made a mistake.
Anyway Thanks again!

[Marcol]

And another way

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[Domski]

I used =INDEX({"Pear","Banana","Apple"},MATCH(A2&B2&C2,{"NNY","NYN","YNN"},0)) as it was the first one working

instead =IF(COUNTIF(A2:C2,"Y")>1,"Too Many Y's",IF(A2="Y","apple",IF(B2="Y","banana",IF(C2="Y","pear","")))) did not work! maybe I made a mistake.

NBVC's is a 'nicer' solution but mine should work fine.

Dom

[TRS]

For sure I made a mistake! Thank you Dom!