Trouble with nested IF OR

[Dreamofsheets]

Hi I'm wondering if anyone can help with an issue I just can't seem to solve.

I'm trying to use a nested IF OR statement to show a value in a cell from another sheet:

=IF(OR(F7,F9,F11=Lists!A1),Lists!C5,IF(OR(F4,F7,F8,F9,F10,F11=Lists!A1),Lists!C6,""))

The first part of the statement works

=IF(OR(F7,F9,F11=Lists!A1),Lists!C5

If the conditions in cells F7,F9 and F11 are met the value in the cell shows Lists!C5

But the second part of the statement does not seem to work

IF(OR(F4,F7,F8,F9,F10,F11=Lists!A1),Lists!C6

Even if all the conditions are met for F4,F7,F8,F9,F10 and F11 the value in the cell does not show Lists!C6

Would someone please be able to suggest changes to my formula to make it work?

Apologies if I have used any incorrect terminology - this is my first attempt at working with the IF OR functions

Thanks

 

[sandy666]

function IF -->> Syntax
function OR -->> technical details

 

[tyija1995]

You're not evaluating any of those F cells except for the last one in your formulae.

So for example, =IF(OR(F4,F7,F8,F9,F10,F11=Lists!A1),Lists!C6) is only looking at F11=Lists!A1

You want to show Lists!C5 value if F7,F9,F11 all equal A1? But show Lists!C6 value if F4,F7,F8,F9,F10,F11 all equal A1 too?

 

[Special-K99]

You cant do it like that, you have to list each OR condition (same with AND)
should be this

=IF(OR(F7=Lists!A1,F9=Lists!A1,F11=Lists!A1),Lists!C5,IF(OR(F4=Lists!A1,F7=Lists!A1,F8=Lists!A1,F9=Lists!A1,F10=Lists!A1,F11=Lists!A1),Lists!C6,""))

Though this still looks wrong, see highlighted in red

IF(F7=Lists!A1 then Lists!C5 otherwise IF(F7=Lists!A1 then Lists!C6
The second IF will never be executed since the first IF will supercede it as it's the same condition
This goes for for F9 and F11 too.
You need to sort your logic out first.

 

[Dreamofsheets]

You cant do it like that, you have to list each OR condition (same with AND)
should be this

=IF(OR(F7=Lists!A1,F9=Lists!A1,F11=Lists!A1),Lists!C5,IF(OR(F4=Lists!A1,F7=Lists!A1,F8=Lists!A1,F9=Lists!A1,F10=Lists!A1,F11=Lists!A1),Lists!C6,""))

Though this still looks wrong, see highlighted in red

IF(F7=Lists!A1 then Lists!C5 otherwise IF(F7=Lists!A1 then Lists!C6
The second IF will never be executed since the first IF will supercede it as it's the same condition
This goes for for F9 and F11 too.
You need to sort your logic out first.

Thank you for this - I can see where I've gone wrong and where the logic is flawed

Maybe my whole approach to the problem is wrong?

I'm trying to assign a text value to a cell based on a respondents answers to a series of questions.

A slightly simplified example of what I'd like to show is below:

Answers:
cell A1 = A
cell A2 = B
cell A3 = A
cell A4 = D
cell A5 = A

in this case if the respondent answers 'A' in cells A1,A3 and A5 I'd like the cell value to be 'Yellow'

Answers:
cell A1 = A
cell A2 = B
cell A3 = A
cell A4 = D
cell A5 = C

in this case if the respondent answers 'A' in cells A1 and A3 only I'd like the cell value to be 'Blue'

So my question is should I be using IF OR at all?

And if this is the case is there another type of formula I could use that is more appropriate?

Cheers!

 

[Special-K99]

IF(AND(A1="A",A3="A"),IF(A5="A",Yellow","Blue"),"SOMETHING ELSE")

If A1 and A3 are A then
If A5 = A (then all 3 are A so return Yellow otherwise only A1 and A3 are A so return Blue
However is A1 or A3 is not A return SOMETHING ELSE

 

[Dreamofsheets]

IF(AND(A1="A",A3="A"),IF(A5="A",Yellow","Blue"),"SOMETHING ELSE")

If A1 and A3 are A then
If A5 = A (then all 3 are A so return Yellow otherwise only A1 and A3 are A so return Blue
However is A1 or A3 is not A return SOMETHING ELSE

Thanks for this - using this logic/function I created the following formula:

=IF(AND(F12="Lists!A1",F14="<wbr style="font-family: Arial, Helvetica, sans-serif; font-size: small;">Lists!A1",F16="Lists!A1"),IF(<wbr style="font-family: Arial, Helvetica, sans-serif; font-size: small;">F9="Lists!A1",F13="Lists!A1",<wbr style="font-family: Arial, Helvetica, sans-serif; font-size: small;">F15="Lists!A1","Lists!C5","<wbr style="font-family: Arial, Helvetica, sans-serif; font-size: small;">Lists!C6")"Lists!C7")

But I get the error message "you have entered too many arguments for this function"

Any advice on what I've gotten wrong here?

 

[Special-K99]

"Lists!A1" is just text, nothing more.
Lists!A1 is a reference to the cell on sheet Lists, you need to remove the double quotes

=IF(AND(F12="Lists!A1",F14="Lists!A1",F16="Lists!A1"),IF(there should be an AND and an opening bracket here F9="Lists!A1",F13="Lists!A1",F15="Lists!A1"missing closing bracket ,"Lists!C5","Lists!C6")there should be a comma here "Lists!C7")

Use this

=IF(AND(F12=Lists!A1,F14=Lists!A1,F16=Lists!A1),IF(AND(F9=Lists!A1,F13=Lists!A1,F15=Lists!A1),Lists!C5,Lists!C6),Lists!C7)