How to Use IF and IFS Functions in Excel

Applies to: Microsoft 365 (Excel for Windows, Excel for Mac, Excel for the Web); Excel 2016, 2019, 2021
Article Type: How-To
Last Updated: 2026-07-29

Summary

IF asks a yes-or-no question about a cell and returns one answer when the answer is yes and another when it is no — the function behind every "Met target", "Overdue", or "Needs approval" column you have ever seen. This article covers the syntax, the quoting rules that break most first attempts, how to stack conditions with nested IF, and how IFS does the same job in a form you can still read next month.

Prerequisites

• Any version of Excel for IF. For IFS, you need Excel 2019, Excel 2021, Microsoft 365, or Excel for the Web.
• A column of values to test.
• No admin rights required.

Instructions

Every example below uses the same sheet, a tab named Q3 Sales:

Row	A	B	C	D	E
1	Sales Rep	Region	Deal Value	Target	Result
2	Priya Raman	North	48200	40000
3	Marcus Webb	South	31750	40000
4	Dana Oyelaran	North	52400	45000
5	Tom Bergstrom	East	18900	25000

1. Understand the Three Arguments

IF asks a yes-or-no question and returns one of two answers depending on the result.

=IF(logical_test, value_if_true, value_if_false)

Argument	What it means	Required
logical_test	A comparison that comes out TRUE or FALSE, such as C2>=D2 or B2="North".	Yes
value_if_true	What the cell shows when the test passes. Text, a number, a cell reference, or another formula.	Yes
value_if_false	What the cell shows when the test fails. Leave it out and Excel displays FALSE, which is rarely what you want.	Technically no — always supply it

The comparison operators available in logical_test:

Operator	Meaning	Example
=	Equal to	B2="North"
<>	Not equal to	B2<>"North"
>	Greater than	C2>40000
>=	Greater than or equal to	C2>=D2
<	Less than	C2<D2
<=	Less than or equal to	C2<=25000

Note: Text comparisons ignore capitalisation. B2="north" matches a cell containing North.

2. Write Your First IF

Flag every sales rep who hit their quarterly target.

1. Open the Q3 Sales tab and click cell E2, under the Result header.
2. Type the formula:

   =IF(C2>=D2, "Met target", "Below target")

3. Press Enter. Cell E2 reads Met target, because 48,200 is above the 40,000 target.
4. Click E2 once so it is selected, then point at the small green square at its bottom-right corner. The pointer changes to a thin black cross.
5. Drag that square down to E5 and release.

Verification: Rows 2 and 4 read Met target; rows 3 and 5 read Below target. If every row shows the same phrase, the cell references were locked with dollar signs — click into the formula bar and remove any $ so C2 and D2 move down with the fill.

3. Quote Text, Leave Numbers Bare

The quoting rules cause more broken IF formulas than anything else.

What you want back	How to write it	What happens if you get it wrong
Text	"Met target" — inside double quotes	Without quotes, Excel reads it as a range name and returns #NAME?
A number	500 — no quotes	"500" returns text that looks like a number but SUM treats as zero
Nothing at all	"" — two quote marks, nothing between	" " puts a real space in the cell, which stops it counting as blank
Another cell's value	C2 — no quotes	"C2" returns the literal letters C2
A calculation	C2*0.05 — no quotes	Quoting it returns the text of the formula

Two examples of the same rule:

=IF(C2>=D2, "Met target", "Below target")

=IF(C2>=D2, C2*0.05, 0)

The first returns words. The second returns a 5% commission on the deal value, or zero — real numbers that add up correctly in a total row.

Tip: If a formula result sits on the left of its cell instead of the right, Excel is treating it as text. Numbers align right by default, so alignment is a fast visual check that your quoting is correct.

4. Chain Conditions with Nested IF

Assign commission tiers by wrapping one IF inside another, in the right order.

When you have more than two outcomes, the value_if_false slot of one IF holds the next IF.

1. Click cell E2.
2. Type the formula:

   =IF(C2>=50000, "Tier 1", IF(C2>=40000, "Tier 2", IF(C2>=25000, "Tier 3", "No tier")))

3. Press Enter. Row 2 (48,200) returns Tier 2.
4. Fill the formula down to E5. Row 4 returns Tier 1, row 3 returns Tier 3, and row 5 returns No tier.

Two rules keep nested IFs honest:

• Order the tests from the most restrictive down. Excel stops at the first test that comes out TRUE. Put C2>=25000 first and every deal above 25,000 comes back as Tier 3, including the 52,400 one — with no error to warn you.
• Close one bracket per IF. Three IFs need three closing brackets at the end. As you type, Excel colours each matching pair, so a bracket left open shows the closing one in a different colour from its partner.

Tip: Long formulas do not have to live on one line. Click in the formula bar, put your cursor before an IF, and press Alt + Enter (Control + Option + Enter on a Mac) to break the formula across lines. Drag the bottom edge of the formula bar down to see them all. The result is unchanged and the logic becomes readable.

5. Replace Nested IF with IFS

IFS does the same job as a stack of nested IFs on a single readable line.

IFS takes pairs — a test, then the result for that test — and returns the result of the first test that passes. No nesting, no bracket-counting.

=IFS(C2>=50000, "Tier 1", C2>=40000, "Tier 2", C2>=25000, "Tier 3", TRUE, "No tier")

1. Click cell E2 and type the formula above.
2. Press Enter. Row 2 returns Tier 2 — the same answer as the nested version in Step 4.
3. Fill down to E5 and compare the column against your nested results. They match.

The parts worth knowing:

• TRUE at the end is the catch-all. TRUE always passes, so anything that reached it gets the final result. Leave it out and rows matching none of the tests return #N/A.
• Order still matters. Like nested IF, IFS stops at the first test that passes.
• Every test needs its own result. An odd number of arguments produces a You've entered too few arguments message.

Note: IFS arrived in Excel 2019. Open a workbook containing it in Excel 2016 or earlier and every IFS cell shows #NAME?. If you share files with a team on mixed versions, the nested IF in Step 4 is the safe choice.

6. Test Two Things at Once with AND and OR

Put AND or OR inside the first argument of IF to check more than one condition.

AND passes when every condition is true. OR passes when at least one is.

• A rep who hit target and works the North region:

  =IF(AND(C2>=D2, B2="North"), "Bonus eligible", "Not eligible")

• A rep in either of two priority regions:

  =IF(OR(B2="North", B2="East"), "Priority region", "Standard")

• Everyone outside one region, using NOT:

  =IF(NOT(B2="South"), "In scope", "Out of scope")

1. Click cell E2 and type one of the formulas above.
2. Press Enter, then fill down to E5.
3. Check a row you already know the answer for. Row 2 is North and above target, so the first formula returns Bonus eligible.

Note: AND and OR each take up to 255 conditions, separated by commas. They go inside IF's first argument — on their own in a cell, they return the words TRUE or FALSE rather than your text.

7. Handle Blanks and Errors

Stop empty rows and upstream errors from filling your report with misleading results.

An empty cell counts as zero in a comparison. In a half-finished sheet, =IF(C2>=D2, "Met target", "Below target") labels every blank row Below target, which reads as real data.

1. Click cell E2.
2. Test for the blank first, and return an empty result when you find one:

   =IF(C2="", "", IF(C2>=D2, "Met target", "Below target"))

3. Press Enter and fill down past the last row of data. The empty rows stay visually empty.

If the cell you are testing is itself a formula that can fail — a VLOOKUP against a list that does not have every code — wrap the whole thing so a broken input does not spread:

=IFERROR(IF(C2>=D2, "Met target", "Below target"), "Check source data")

Verification: Scroll to the first empty row below your data. It shows nothing at all rather than Below target or FALSE. That is what a chart or PivotTable built on this column needs in order to ignore it.

Troubleshooting

TIP

Build long IF and IFS formulas one condition at a time. Get =IF(C2>=D2, "Met target", "Below target") returning the right answer first, then add the next tier and check again. A formula that worked a moment ago tells you exactly which addition broke it — far faster than staring at a finished 90-character formula wondering where the logic went wrong.

Symptom / Error	Potential Cause	Solution
#NAME?	Text result written without quotes	Put double quotes around every word you want returned — see Step 3. If the formula uses IFS, check your Excel version instead.
The cell shows FALSE	Third argument left out	=IF(C2>=D2, "Met target") has nothing to show when the test fails. Add the third argument.
The formula text appears instead of a result	Cell is formatted as Text	Select the cell, on the Home tab set the Number Format dropdown to General, then press F2 and Enter to re-enter the formula.
You've entered too few arguments	An IFS test without its result	Every IFS test needs a result immediately after it. Count your arguments — the total is always an even number.
#N/A from an IFS formula	No test matched and no catch-all	Add , TRUE, "No tier" as the final pair, as shown in Step 5.
Every tier comes back the same	Tests ordered from smallest up	Excel stops at the first passing test. Order tests from the most restrictive down — see Step 4.
Results do not add up in a total row	Numbers returned inside quotes	"500" is text. Remove the quotes so the result is a real number.

Related Articles

• How to Use SUMIF and COUNTIF in Excel
• How to Use VLOOKUP in Excel
• How to Use XLOOKUP in Excel
• How to Fix Common Excel Formula Errors
• How to Use Conditional Formatting in Excel
• Understanding Excel Features — A Quick-Reference Guide to What Excel Can Do

Did this solve your problem?