IF, AND and OR in Excel: how to make decisions with formulas

Many spreadsheets need to make decisions: flag an order as urgent, check whether a customer gets a discount or warn you when an amount goes over a limit. Doing it by hand, row by row, is slow and error-prone. Excel handles this with three logical functions that work together: IF, AND and OR.

Below you will see how each one works, how to combine them in a single formula, when nested IFs or IFS make sense and which mistakes tend to show up. All the examples use a small order table you can copy to practice.

The sample data

We start from this table, with headers in row 1 and data from row 2:

Type the formulas in row 2 of an empty column, then copy them down by dragging the fill handle.

How the IF function works

IF tests a condition and returns one value if it is met and a different value if it is not. Its syntax is:

=IF(logical_test,value_if_true,value_if_false)

• logical_test: a comparison that returns TRUE or FALSE, such as B2>=1000.
• value_if_true: what appears when the condition is met. It can be text, a number or another formula.
• value_if_false: what appears when it is not. If you leave it out, Excel shows FALSE.

To classify orders by size:

=IF(B2>=1000,"Large","Standard")

Customer A and Customer D get “Large”; the rest get “Standard”. Text always goes inside double quotes; numbers and cell references do not.

Comparison operators

Inside the logical test you can use = (equal to), <> (not equal to), >, <, >= and <=. Text comparisons are not case-sensitive: C2="yes" and C2="Yes" give the same result. Accents and extra characters, however, do count: “Cafe” is not equal to “Café”.

AND and OR: testing several conditions

The AND and OR functions do not return text, only TRUE or FALSE. Their job is to group several conditions:

• AND returns TRUE only if all conditions are met.
• OR returns TRUE if at least one is met.

For example, to check whether an order is large and also paid:

=AND(B2>=1000,C2="Yes")

Only Customer A meets both conditions. This other formula checks whether the order is large or paid:

=OR(B2>=1000,C2="Yes")

Here customers A, C and D return TRUE. Only Customer B, with a low amount and no payment, returns FALSE. Both functions accept up to 255 conditions, although with more than four or five the formula becomes hard to read.

Combining IF with AND and OR

The real power appears when you use AND or OR as the logical test of IF. That turns a bare TRUE or FALSE into a useful message.

Example with AND

Suppose you send a free sample with large orders that are already paid:

=IF(AND(B2>=1000,C2="Yes"),"Send gift","")

The empty quotes at the end leave the cell blank when the condition is not met, which keeps the column cleaner.

Example with OR

Now imagine that the North and South regions get free shipping and the rest pay for delivery:

=IF(OR(D2="North",D2="South"),"Free shipping","Paid shipping")

AND and OR in the same formula

You can also put one function inside the other. This formula flags as priority the paid orders that are large or going to the North region:

=IF(AND(C2="Yes",OR(B2>=1000,D2="North")),"Priority","Standard")

Here is the result of the three formulas with the sample data:

Nested IFs and IFS

When you need more than two outcomes, you can place one IF inside another. To sort orders into three sizes:

=IF(B2>=1000,"Large",IF(B2>=500,"Medium","Small"))

Excel evaluates the conditions in order and stops at the first one that is met. That is why order matters: if you tested B2>=500 first, an order of 1900 would end up as “Medium”.

In Excel 2019, Excel 2021 and Microsoft 365 there is a more readable alternative, IFS, which avoids stacking parentheses:

=IFS(B2>=1000,"Large",B2>=500,"Medium",TRUE,"Small")

The last pair, with TRUE as its condition, works as an “in any other case”. If you leave it out and no condition is met, the formula returns the #N/A error.

Tips to avoid getting lost in nested formulas:

1. Write down the conditions in order first, from the most restrictive to the most general.
2. Check that every open IF has its closing parenthesis. Excel colors matching pairs of parentheses as you type.
3. If you go beyond three or four levels, consider a lookup table of ranges with VLOOKUP in approximate match mode.
4. Test the formula with boundary values (exactly 500 or 1000) to confirm they land in the right group.

Using IF to compare two columns

Another very common use is checking whether two lists match row by row, for example this month’s prices against last month’s. If the values are in columns A and B:

=IF(A2=B2,"Same","Different")

This comparison is not case-sensitive. If you need “PROD-1” and “prod-1” to count as different, use the EXACT function:

=IF(EXACT(A2,B2),"Same","Different")

With numbers, two values sometimes look identical but one is stored as text, and the formula returns “Different”. Check the alignment (numbers are right-aligned by default) or convert the text with VALUE.

This method works when both lists are on the same sheet and in the same order. If you want to compare two whole files, our free tool to compare Excel files checks both versions and generates a file with the changes, without writing a single formula.

Common mistakes with IF, AND and OR

• Forgetting the quotes around text. If you type C2=Yes without quotes, Excel reads Yes as a defined name and returns #NAME?.
• Chaining comparisons like in math. 500<B2<1000 does not work as expected: Excel first evaluates 500<B2, gets TRUE or FALSE, and then compares that result with 1000. The correct way is AND(B2>500,B2<1000).
• Using the wrong separator. Depending on your regional settings, arguments are separated by commas or by semicolons. If Excel rejects the formula, try the other separator.
• Numbers stored as text. Excel treats any text as greater than any number, so B2>=1000 returns TRUE even if the amount is the text “250”. This often happens with data imported from CSV.
• Invisible spaces. “North ” with a trailing space is not equal to “North”. Clean the data with TRIM before comparing.

Conclusion

IF chooses between two results, AND requires every condition to be met and OR is happy with just one. Together they cover most of the rules you will find in a spreadsheet: discounts, priorities, alerts or validations.

Start with simple formulas, check the results with boundary values and, when the logic grows, move to IFS or helper columns. A formula you can understand at a glance is much easier to fix a few months later.

Frequently asked questions