Excel IF With Multiple Conditions

The distinction that decides which one you need

Two different questions get muddled here, and choosing the wrong tool is why these formulas get so tangled.

You want	Use
One answer, but several things must be true	IF with AND
One answer, if any of several things is true	IF with OR
Several different answers depending on a value	IFS (or nested IF)
Several different answers from an exact match	SWITCH

AND and OR do not produce answers — they produce TRUE or FALSE, which IF then acts on. That is why they sit inside IF rather than replacing it.

AND: every condition must be true

=IF(AND(A2>100, B2="Yes", C2<>""), "Approved", "Review")

All three must hold. IF takes three parts: the test, what to return if true, and what to return if false. AND simply supplies a more elaborate test.

You can pass many conditions to AND — the limit is well beyond anything readable.

OR: any one is enough

=IF(OR(B2="Urgent", A2>1000), "Priority", "Standard")

Either condition triggers it. And the two combine:

=IF(AND(A2>100, OR(B2="Yes", B2="Maybe")), "Ship", "Hold")

Read that from the inside out: B2 is Yes or Maybe, and A2 is over 100.

IFS: several different answers

IFS takes pairs — condition, result, condition, result — and returns the result of the first condition that is true.

=IFS(A2>=90,"A", A2>=80,"B", A2>=70,"C", A2>=60,"D", TRUE,"F")

Order is everything. IFS stops at the first match, so conditions must run from most to least restrictive. Reverse that list and every score above 60 returns "D", because A2>=60 is true for a 95 as well.

Always finish with TRUE, "something". TRUE is always true, so it catches everything that fell through — the equivalent of "else". Without it, an unmatched value returns #N/A.

Nested IF: the older equivalent

Before IFS, you put an IF inside the "if false" slot of another IF:

=IF(A2>=90,"A", IF(A2>=80,"B", IF(A2>=70,"C", "F")))

It works identically and everywhere, but it is harder to read and harder to edit — every level adds a closing bracket at the end, and a missing one is the usual reason it will not enter.

Order matters here for exactly the same reason: Excel evaluates outward-in and stops at the first true test.

Excel allows up to 64 levels of nesting. Readability collapses long before that — beyond three or four, use IFS, SWITCH, or a lookup table with VLOOKUP or XLOOKUP, which is usually the better answer for anything with many bands.

SWITCH: exact matches

When you are comparing one value against a list of exact possibilities rather than ranges:

=SWITCH(B2, "N","North", "S","South", "E","East", "W","West", "Unknown")

Shorter than the IFS equivalent because the value being tested is stated once. The final lone argument is the default. SWITCH cannot do greater-than comparisons — for ranges you need IFS.

Why these formulas fail

Symptom	Cause
#N/A from IFS	No condition matched and there is no TRUE catch-all
#NAME?	IFS or SWITCH on a version too old to have them
Every row gives the same answer	Conditions in the wrong order — the loosest one is matching first
"You've entered too few arguments"	IFS given an odd number of arguments; every condition needs a result
Numbers not comparing	They are text, not numbers — see why SUM is not working
Text comparison fails	Trailing spaces; wrap in TRIM()
Blank cells behave oddly	=A2="" is true for a blank; use ISBLANK(A2) to be explicit

Testing a formula that misbehaves

Rather than staring at the whole thing, evaluate the pieces. Put =AND(A2>100, B2="Yes") in a spare cell on its own — it returns TRUE or FALSE, telling you immediately whether the test is wrong or the results are.

For a long nested formula, select part of it in the formula bar and press F9. Excel shows what that fragment evaluates to. Press Esc afterwards — pressing Enter would replace the formula with the value.

Frequently asked questions

How do I write an IF formula with multiple conditions?

If all conditions must be true, put AND inside IF: =IF(AND(A2>100,B2="Yes"),"Ship","Hold"). If any one is enough, use OR the same way. For several different answers, use IFS instead.

What is the difference between IFS and nested IF?

They do the same job. IFS takes condition/result pairs in a flat list and is much easier to read; nested IF puts each new IF in the previous one's "if false" slot. IFS needs Excel 2019 or 365; nested IF works everywhere.

Why does my IFS formula return #N/A?

Because no condition matched and there is no catch-all. End the formula with TRUE, "something" — TRUE is always true, so it acts as the "else".

Why does every row give the same answer?

The conditions are in the wrong order. IFS and nested IF stop at the first true test, so a loose condition placed early catches everything. Order from most restrictive to least.

How many IFs can I nest?

Up to 64 in modern Excel, but readability fails long before. Past three or four levels, switch to IFS, SWITCH, or a lookup table with VLOOKUP or XLOOKUP.

Why do I get #NAME? on IFS?

Your version of Excel does not have it — IFS arrived in Excel 2019 and 365. Use nested IF instead, which works in every version.

How do I check which part of my formula is wrong?

Put the condition alone in a spare cell to see whether it returns TRUE or FALSE. Or select part of the formula in the formula bar and press F9 to evaluate just that fragment, then press Esc.

Conclusion

AND and OR go inside IF, because they produce TRUE or FALSE rather than answers. IFS replaces stacks of nested IFs and is far easier to maintain — just remember the TRUE catch-all at the end and keep conditions ordered most restrictive first.

If a formula has grown past four levels, the answer is usually not a better formula. It is a small lookup table.

Related: Excel formula errors explained covers the error codes, and why a formula is not calculating covers results that never appear.