Excel IF formula with AND & OR logical functions

If you want to test the result of cells based on several sets of multiple test conditions, you can use the IF function with the AND and OR functions at a time.

Assuming that you have the following test logic to check the exam scores:

Logic test1: 60<=B1<=70

Logic test2: 60<=C1<=70

If any of the above conditions are met, then mark the result as “good”, otherwise, mark the result as “bad”. We need to use two AND functions to reflect the above two logical test and enclose them in the OR function as one logical test in the Excel IF formula. Let’s write down the below IF formula with AND & OR logical functions:

=IF(OR(AND(B1>=60,B1<=70),AND(C1>=60,C1<=70)),"good","bad") 

Excel IF formula with AND OR logical functions1

Let’s see another IF formula with AND and OR functions as follows:

=IF(AND(B1="tom",OR(C1>=60,D1>=60)),"pass","")

When B1 is equal to “tom”, and either C1 is greater than or equal to “60” or D1 is greater than or equal to 60, the formula returns “pass”. Otherwise, the formula will return an empty string.

Excel IF formula with AND OR logical functions2

If we just want to check the cells where the value in column B is “tom” AND the score in column C is greater than or equal to 60. If you want to return TRUE, you can use the below logical test statement with the AND function:

AND(B1="tom",C1>=60)

Next, we can extend the above statement, we want to check the cells if the values in column B is “tom” AND the scores in column C or column D is greater than or equal to 60. Then we can nest the OR function in the above AND function like below:

AND(B1="tom",OR(C1>=60,D1>=60))

Related Formulas

  • Excel IF formula with equal to logical operator
    The “Equal to” logical operator can be used to compare the below data types, such as: text string, numbers, dates, Booleans.  This section will guide you how to use “equal to” logical operator in excel IF formula with text string value and dates value…
  • Excel IF formula with OR logical function
    If you want to check if one of several conditions is met in your excel workbook, if the test is TRUE, then you can take certain action. You can use the IF function combining with OR function to construct a condition statement…
  • Excel IF formula with AND logical function
    You can use the IF function combining with AND function to construct a condition statement. If the test is FALSE, then take another action…

Related Functions

  • Excel AND function
    The Excel AND function returns TRUE if all of arguments are TRUE, and it returns FALSE if any of arguments are FALSE.The syntax of the AND function is as below:= AND (condition1,[condition2],…) …
  • Excel OR function
    The Excel OR function used to test multiple conditions and returns TRUE if any of the conditions are TRUE. Otherwise, it will return FALSE. The syntax of the OR function is as below:=OR(logical1, [logical2], …)…
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE….
  • Excel nested if function
    The nested IF function is formed by multiple if statements within one Excel if function. This excel nested if statement makes it possible for a single formula to take multiple actions…

 

Related Posts

Extract matching values From Two Lists
extract matching values from two list1

Suppose that you are working with two lists containing few values, and you want to extract the matching values from those two lists into another separate list. You might prefer to manually extract the matching values from the two lists, ...

Extract Multiple Match Values into Separate Columns

If you have a few values/items in the excel sheet and you are thinking that with the aid of the “VlOOKUP” function you can look for a specific value, extract it and then put the matching item into the separate ...

VLOOKUP Formula | Faster Trick with 2 VLOOKUPS

This post will guide you how to use 2 VLOOKUPS function to looking up data entries from a given range of cells in Microsoft Excel. VLOOKUP with 2 lookups can be faster than a single VLOOKUP in certain scenarios. The ...

Extract all the matches with helper Column
how to extract all matches with helper column1

With Excel's powerful functions IF, INDEX, and MATCH, we can find exactly what you're looking for with a few clicks of the mouse. This step-by-step tutorial will show how easy it is to extract data using these tools and more! ...

If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of ...

If Value is Greater Than A Certain Value
If Value is Greater Than A Certain Value 1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, ...

If Cell is Not Blank
If Cell is Not Blank 6

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell is Blank
If Cell is Blank_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell Equals Certain Text String
If cell equals certain text_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you ...

If Cell Contains Either Text1 or Text2
If cell contains text1 or text2_1

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true ...

Sidebar