Excel Logical Functions: IF, AND, OR, NOT, and More

Excel logical functions turn business rules into formulas. They can flag an item for reorder, test whether several requirements are met, or return a different result when a condition is true.

The key is to separate the job into three parts: write a test, combine tests when necessary, and decide what Excel should return. This guide develops that method with one inventory worksheet so you can see where IF, AND, OR, NOT, IFS, and SWITCH belong.

Choose the logical function that matches the decision

AND, OR, and NOT return the logical values TRUE or FALSE. Put one inside IF only when you want to translate that logical result into a label, number, calculation, or blank-looking result.

Set up the inventory example

Imagine that each worksheet row represents a product. The example formulas use these columns:

With these values, stock is at or below the reorder point, the supplier is active, and the item is in the Critical category. You can change the inputs later to verify both the true and false paths.

Start with a comparison

A logical formula usually begins with a comparison. To test whether the stock in B2 is at or below the reorder point in C2, enter:

=B2<=C2

A simple logical test compares Units in Stock in B2 with the Reorder Point in C2. Because 8 is less than or equal to 10, the formula returns TRUE.

The example returns TRUE because 8 is less than or equal to 10. A bare comparison is often all you need for a helper column, a data-validation rule, or a conditional-formatting formula.

Put literal text such as Active in double quotation marks. Do not quote a cell reference or a number you want Excel to treat as numeric. A reference written as "B2" is merely the text B2, not the value stored in cell B2.

Return two possible results with IF

The IF function evaluates a logical test, returns its second argument when the test is true, and returns its third argument when the test is false. Its syntax is:

=IF(logical_test, value_if_true, value_if_false)

To display a stock instruction instead of TRUE or FALSE, use:

=IF(B2<=C2,"Reorder","Sufficient")

This IF formula checks whether Units in Stock in B2 are less than or equal to the Reorder Point in C2. When the condition is true, Excel returns "Reorder"; otherwise, it returns "Sufficient".

With the example data, the formula returns Reorder. If B2 changes to 11, the test becomes false and the formula returns Sufficient.

The two result arguments do not have to be text. They can be numbers, cell references, calculations, other functions, or "" when you want the cell to display nothing. Note that "" is an empty text string; it can look blank without making the formula cell truly empty.

1. Write the rule in plain language: “If stock is at or below the reorder point, show Reorder; otherwise show Sufficient.”
2. Translate only the test: B2<=C2.
3. Add the result for a true test: "Reorder".
4. Add the result for a false test: "Sufficient".
5. Test the formula with values that should follow each branch.

Microsoft’s IF function reference documents the arguments and additional examples.

Require every condition with AND

AND returns TRUE only when every supplied condition is true. This formula asks whether the product needs replenishment and its supplier is active:

=AND(B2<=C2,D2="Active")

The AND function tests whether Units in Stock in B2 are less than or equal to the Reorder Point in C2 and whether Supplier Status in D2 equals Active. Because both conditions are true, the formula returns TRUE.

Both tests are true in the example, so the result is TRUE. If either condition is false, AND returns FALSE.

Wrap the test in IF when a descriptive result is more useful:

=IF(AND(B2<=C2,D2="Active"),"Place order","Review")

Read the formula from the inside out. AND determines whether both requirements are met; IF converts that result into an instruction.

Allow either condition with OR

OR returns TRUE when at least one supplied condition is true. To escalate an item when it is classified as Critical or has no stock, use:

=OR(E2="Critical",B2=0)

The OR function checks whether the item category in E2 is Critical or the units in stock in B2 equal 0. Because the category is Critical, the formula returns TRUE even though the stock value is 8.

The example returns TRUE because the category is Critical, even though stock is not zero. To return a label instead, use:

=IF(OR(E2="Critical",B2=0),"Escalate","Normal")

Choose AND when the rule says “all,” “both,” or “every.” Choose OR when it says “any,” “either,” or “at least one.”

Reverse a test with NOT

NOT flips a logical result: TRUE becomes FALSE, and FALSE becomes TRUE. This test checks that the supplier status is not Blocked:

=NOT(D2="Blocked")

The NOT function reverses the result of the test D2="Blocked". Because the supplier status in D2 is Active, the original test is FALSE and NOT returns TRUE.

Because D2 contains Active, the equality test is false and NOT reverses it to TRUE. For a simple “not equal” test, the shorter equivalent is:

=D2<>"Blocked"

NOT becomes more useful when it reverses a larger expression. For example, =NOT(OR(D2="Blocked",D2="Paused")) is true only when the supplier is neither Blocked nor Paused.

See how AND, OR, and NOT evaluate results

This table also reveals a common source of errors: one accidental OR can make a rule far broader than intended, while one accidental AND can make it too restrictive.

Use IFS for ordered conditions

A long chain of nested IF functions can be difficult to audit. IFS checks condition-and-result pairs from left to right and returns the result belonging to the first true condition.

=IFS(B2=0,"Out of stock",B2<=C2,"Reorder",TRUE,"Sufficient")

The IFS function checks each condition from left to right and returns the result for the first true condition. Here, B2 is not 0, but B2 is less than or equal to C2, so the formula returns "Reorder".

The order matters. A stock value of zero satisfies both B2=0 and B2<=C2, so the more specific zero test comes first. The final TRUE,"Sufficient" pair acts as a default. Without a true condition or a default, IFS returns #N/A.

Use IFS for ordered rules such as thresholds and priority bands. Microsoft’s IFS reference explains its condition-and-result pairs.

Use SWITCH for exact-value mappings

SWITCH is a cleaner choice when one cell or expression can contain several exact values. Suppose category codes A, B, and C correspond to different service levels:

=SWITCH(E2,"A","Urgent","B","Standard","C","Low","Unknown")

The SWITCH function compares the value in E2 with a list of exact matches and returns the corresponding result. Because E2 contains B, the formula returns "Standard".

SWITCH compares E2 with each listed value and returns the result for the first match. The final "Unknown" is an optional default. If no value matches and no default is supplied, the function returns #N/A.

SWITCH is designed for exact matches, not inequalities such as “less than 10.” Use IFS for ordered thresholds. If the mapping grows into a long list that users must maintain, put the codes and labels in a lookup table instead of embedding every pair in the formula. See Microsoft’s SWITCH function reference for full syntax details.

Keep complex logical formulas readable

• Use direct logic when possible. Write =AND(B2<=C2,D2="Active"), not =IF(AND(B2<=C2,D2="Active"),TRUE,FALSE), when a Boolean result is all you need.
• Name the rule before writing the formula. Clear words make it easier to choose between AND and OR.
• Use parentheses to show groups. In mixed logic, they make the intended evaluation visible even when Excel would calculate the same result without extra grouping.
• Use helper columns for separate decisions. Two short, labeled tests are often easier to review than one deeply nested formula.
• Test boundary values. For a threshold of 10, check 9, 10, and 11 so you can catch an incorrect < or <=.
• Keep repeated labels consistent. Extra spaces and inconsistent wording can stop exact text comparisons from matching as expected.

Excel commonly uses commas between function arguments. Depending on your regional settings, it may require semicolons instead. Use the separator shown by Formula AutoComplete in your installation.

Microsoft provides additional combined patterns in its guide to using IF with AND, OR, and NOT.

Troubleshoot logical formulas

Once the underlying calculation is correct, IFERROR can provide a deliberate fallback for formula errors. For example:

=IFERROR(F2/G2,0)

This returns zero if the division produces an error. Use it carefully: a broad fallback can hide a broken reference, invalid input, or another problem that deserves attention. Microsoft lists the error types handled by IFERROR in its function reference.

Do not add IFERROR merely to make an unfinished formula look clean. First confirm that the formula returns the expected result for valid inputs, then decide which errors should receive a fallback.

A practical workflow for any logical formula

1. Describe the decision in one sentence.
2. Identify the cell references, comparison operators, and boundary values.
3. Enter each comparison by itself and confirm that it returns the expected TRUE or FALSE.
4. Combine tests with AND, OR, or NOT only if the rule requires it.
5. Use IF, IFS, or SWITCH when the worksheet needs a result other than a Boolean value.
6. Test normal inputs, boundaries, blanks, and unexpected values before copying the formula down.

Frequently asked questions

Build the test first, then choose the output

Reliable logical formulas begin with a test you can explain and verify. Use AND, OR, and NOT to shape that test, then add IF only when you need different outputs. When a rule has several ordered outcomes, move to IFS; when one value maps to several exact labels, use SWITCH.

That test-first approach makes formulas easier to build, review, and repair. It also makes the worksheet’s decision rules visible to the next person who has to maintain them.