Can a logical test work for this?

[jakefh]

Hello.

I have 4 possible outcomes from 2 cells. I've tried logical test combinations without success.

I would like column C to return "E&M", "None", "Need English" or "Need maths" based on the references in columns A and B.

The A and B columns only have "Y" or "N" in them, so I've listed the possible combinations and what I would like to be returned in column C.

I have 2007 at work, but could use 2010 at home if needed.

Thanks in advance to anyone who can help.

Jake

 

[Somendra Misra]

Hi Jakefh....

Based on the possible output provided by you in file...place this formula in C2 and copy down.

=IF(AND(A2="N",B2="N"),"None",IF(AND(A2="Y",B2="Y"),"E&M",IF(AND(A2="Y",B2="N"),"Need E",IF(AND(A2="N",B2="Y"),"Need M"))))

Tell if you want anything different from this.

Somendra.

 

[jakefh]

Wonderful! Thank you very much.

 

[Hui]

What about this much briefer formula:
=CHOOSE((A2="N")+2*(B2="N")+1,"E&M","Need M","Need E","None")
From 123 down to 62 Characters

 

[Somendra Misra]

@Hui ...

Awesome.

 

[shrivallabha]

One more:

=LOOKUP(A2&B2,{"NN","NY","YN","YY"},{"None","Need M","Need E","E&M"})

The sequence matters for LOOKUP though.

Or you can create LOOKUP table also and use VLOOKUP.

 

[Hui]

I can't get it less than 62 characters
But heres another go also at 62 Characters
=INDEX({"E&M";"Need M";"Need E";"None"},(A2="N")+2*(B2="N")+1)

 

[salmonchild]

IT'S A FORMULA WALK OFF!

 

[Xiq]

I can't get it less than 62 characters
But heres another go also at 62 Characters
=INDEX({"E&M";"Need M";"Need E";"None"},(A2="N")+2*(B2="N")+1)

Maybe this will help :

="Need "&INDEX({"E&M";"M";"E";"None"},(A2="N")+2*(B2="N")+1)

Only 60 chars!

 

[SirJB7]

@Xiq
Hi!
Doesn't work for "None" and "E&M", adds a previous unwanted "Need ".
Regards!