How to do multiple if statements in excel

Answer

To do multiple IF statements in Excel, use one of these methods: nested IF, IFS, IF combined with AND/OR, SWITCH, or INDEX/MATCH. Choose the method that is clearest and easiest to maintain for your situation.

Method 1 — Nested IF (classic)

A nested IF places one IF inside another to test multiple conditions in order.

1. Use nested tests when each condition is checked sequentially.

2. Place the most likely or highest-priority test first.

3. Watch out for readability; many levels become hard to maintain.

Example formula (grade from score in A2):

\texttt{=IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","F"))))}

Notes:

1. Modern Excel supports up to 64 nested IF levels.

2. Older Excel versions prior to 2007 permitted only 7 nested levels.

Method 2 — IFS (cleaner for many mutually exclusive conditions)

IFS evaluates pairs of condition/result without deep nesting and stops at the first TRUE.

1. Use IFS when conditions are mutually exclusive and you have Excel that supports it.

2. Provide a final TRUE condition to act as a default catch-all.

Example formula (same grade example):

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

Compatibility note:

1. IFS is available in Excel for Microsoft 365 and modern standalone Excel versions.

2. If IFS is unavailable, use a nested IF.

Method 3 — IF with AND / OR (composite conditions)

Combine IF with AND or OR when a single test depends on multiple criteria.

1. Use AND when all conditions must be true.

2. Use OR when any single condition being true suffices.

Example: bonus if sales in A2 exceed 100 and region in B2 equals "East":

\texttt{=IF(AND(A2>100,B2="East"),"Bonus","No Bonus")}

Example: apply discount when product is "A" or "B":

\texttt{=IF(OR(C2="A",C2="B"),"Discount","No Discount")}

Method 4 — SWITCH (exact match branching)

SWITCH selects a result based on an expression matching one of several values.

1. Use SWITCH for clean exact-value branching.

2. Provide a default value at the end.

Example: return department name using code in A2:

\texttt{=SWITCH(A2,1,"Sales",2,"HR",3,"IT","Unknown")}

Compatibility note:

1. SWITCH exists in Microsoft 365 and recent Excel versions.

2. Legacy Excel versions may not support SWITCH.

Method 5 — INDEX + MATCH or LOOKUP (scalable and maintainable)

Use a lookup table when many conditions map to outcomes; this keeps formulas short and editable.

1. Create a two-column table: criteria in column E, results in column F.

2. Use MATCH to find the row and INDEX to return the result.

Example using exact match with lookup table in E:F:

\texttt{=INDEX(F:F,MATCH(A2,E:E,0))}

Use VLOOKUP with exact match as an alternative:

\texttt{=VLOOKUP(A2,E:F,2,FALSE)}

Best practices and debugging

1. Order tests by priority to avoid incorrect early matches.

2. Use a default or catch-all result at the end to handle unexpected inputs.

3. Use named ranges or lookup tables for complex mappings to simplify edits.

4. Wrap comparisons with TRIM or UPPER when text inconsistencies may occur.

5. Use Evaluate Formula on the Formulas tab to step through nested logic.

6. Avoid very long nested IF chains when IFS, SWITCH, or lookup tables will be clearer.

Windows vs Mac differences

1. Function names and behavior are the same on current Windows and Mac Excel versions.

2. IFS, SWITCH, and dynamic-array behavior require Microsoft 365 or recent perpetual-license builds on both platforms.

3. Keyboard shortcuts for formula auditing or entering array formulas differ by platform, but modern dynamic arrays remove most array-entry differences.

Quick reference formulas (copy-ready LaTeX code blocks)

Nested IF (grades):

\texttt{=IF(A2>=90,"A",IF(A2>=80,"B",IF(A2>=70,"C",IF(A2>=60,"D","F"))))}

IFS (grades):

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

IF with AND:

\texttt{=IF(AND(A2>100,B2="East"),"Bonus","No Bonus")}

SWITCH (codes):

\texttt{=SWITCH(A2,1,"Sales",2,"HR",3,"IT","Unknown")}

INDEX + MATCH lookup:

\texttt{=INDEX(F:F,MATCH(A2,E:E,0))}

Common questions (Q&A)

How do I choose between nested IF and IFS?

Choose IFS when it is available and your conditions are mutually exclusive because IFS is easier to read and maintain. Choose nested IF for compatibility with older Excel versions.

Can I use multiple conditions that must all be true?

Yes. Use AND within an IF to require multiple true conditions, for example:

\texttt{=IF(AND(A2>100,B2="East"),"Bonus","No Bonus")}

What if I have many mappings that might change?

Use a lookup table with INDEX/MATCH or VLOOKUP. This keeps logic in a table you can edit without rewriting formulas.

Use the approach that keeps your workbook easiest to read and maintain.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report