IF & IF OR & AND IF Statements (Google Sheets)-CodePudding

Trying to create an automated checker. If people choise the rights answers from a Dropdown box they will see the msg: "You May Now Consult" if not they will see "You shall not pass"

I have the following code:

=IF(OR(C8="1", "2")(D8="Yes")(E8="Yes")(G8="Reusable")(F8="Yes"), "You May now Consult", "You Shall Not Pass")

The issue is just the first part, C8. Since it's a dropdown box people can choise multiple answers.

Imagine there are the numbers 1-5 in the dropdown box, but I only want the true statement to be if they choise 1 or 2. The issue now is If I put in number 3 it will give me the error code #VALUE! error.

How Could i create this code so that if they choose numbers 3-5 it will give the error msg "You Shall not Pass"?

Sorry for my explanation as I never really coded anything and this is my first project.

Thanks in advance!

Tried to add the IF(NOT statement but it does not seem to match with the (IF(OR statement.

CodePudding user response：

You can fix syntax errors in the formula like this:

=if( 
  or( C8 = "1", C8 = "2", D8 = "Yes", E8 = "Yes", G8 = "Reusable", F8 = "Yes"), 
  "You May now Consult", 
  "You Shall Not Pass" 
)

CodePudding user response：

use:

=IF((C8="1") (C8="2") (D8="Yes") (E8="Yes") (G8="Reusable") (F8="Yes"), 
 "You May now Consult", "You Shall Not Pass")

CodePudding user response：

Your formula:

IF(
 OR(
  C8=1,
  C8=2,
  D8="Yes",
  E8="Yes",
  G8="Reusable",
  F8="Yes"
 ),
 "You May now Consult",
 "You Shall Not Pass"
)

When you use an OR() function, it returns true whenever any of the conditions are matched, in you case, either C8 is 1 or 2, or D8 or E8 or F8 is "Yes", or G8 is "Reusable".

So, even if you put a 3 into C8, if there are "Yes" in any of D8:F8, or "Reusable" is shown in G8, it will still returns TRUE.

But if the value of C8 must be 1 or 2, in that case, you need a AND():

IF(
 AND(
  OR(
   C8=1,
   C8=2,
  ),
  OR(
   D8="Yes",
   E8="Yes",
   G8="Reusable",
   F8="Yes"
  )
 ),
 "You May now Consult",
 "You Shall Not Pass"
)

With this formula, it will return TRUE only when C8 is equal to 1 or 2 AND either D8:F8 contains a "Yes" or G8 equals to "Reusable"