Which Excel function returns one value if a specified set of conditions is true and another value if the specified set of conditions is false?

How to Use Excel IF Function Examples

Use the Excel IF function when you want to test something, and show one result if the test result is True, and a different result if the test result is False. In Excel 365, there is also an IFS function, which can replace multiple newsted IF functions.

NOTE: In many cases, another Excel function would be more efficient than a complex IF formula. See the examples below.

Introduction - IF Function

The Excel IF function is helpful if you want to check something on a worksheet, then show result A or B. This short video shows the steps for setting up a simple IF formula. There are written steps below.

Video Timeline

• 0:00 Introduction
• 0:13 Do a Test
• 0:39 Order Form
• 0:55 Simple IF Formula
• 1:39 Test the Formula

How the Excel IF Function Works

IF is one of the Logical functions in Microsoft Excel, and there are 3 parts (arguments) to the IF function syntax:

1. logical_test: TEST something, such as the value in a cell.
2. value_if_true: Specify what should happen if the test result is TRUE.
3. value_if_false: Specify what should happen if the test result is FALSE.

Ex 1: Check for Empty Cell

In this example, the Total in cell E7 should only show an amount if the Quantity has been entered in cell D7.

These are the 3 parts (arguments) to the IF function for this example:

1. logical test: TEST cell D7, to see if it is empty (D7=""),.
2. value if true: If D7 is empty (TRUE), the cell with the Total formula will show nothing ("").
3. value if false: If D7 is not empty (FALSE), the cell with the Total formula will multiply Price x Quantity (C7*D7)

The completed formula is:  =IF(D7="","",C7*D7)

In the screen shot below, cell D7 is empty (TRUE result), so the IF formula in cell E7 also looks empty.

When cell D7 is not empty (FALSE result), the Total cell calculation shows the value of Price x Quantity.

Ex 2: Nested IF Functions

If you need to do more than one logical test in a formula, you can combine multiple IF functions in one formula. This is called a nested formula, because the additional IF functions act as arguments, nested within another IF function.

One Logical Test

In Example 1, the IF formula had one logical test -- is the Qty cell (D7) empty?

• =IF(D7="","",C7*D7)

Two Logical Tests

To check both the Price and Quantity cells, another IF function is added to the existing formula.

That creates a second logical test -- is the Price cell (C7) empty?

• =IF(C7="","", IF(D7="","", C7*D7)

The results for different scenarios are shown in the screen shot below.

1. In row 7, the Price cell is empty, so the Total cell has an empty string ("")
2. In row 8, the Qty cell is empty, so the Total cell has an empty string ("")
3. In row 9, neither cell is empty, so the total price is calculated (price x quantity)

Order of Nested IF Functions

In the previous nested IF formula example, there were 2 logical tests:

• Is the Price cell empty?
• Is the Qty cell empty?

Those tests were equally difficult.

• It didn't matter which test was done first.
• The formula result would be the same, with the tests in either order.

How Difficult are the Tests?

In some cases, the logical tests in a nested IF formula have different levels of difficulty.

For example, we need to build a formula where product scores are grouped into the following rating categories:

Logical Test Order of Difficulty

The 3 logical tests, above, are listed in order of difficulty:

1. In this case, the "Good" test is hardest - Is the product score 80 or higher?
• Only a few products will have high scores, and they will get the "Good" rating.
2. The "Average" test is second hardest - Is the product score 60 or higher?
   • Most of the products will have scores of 60 or more, and they will get the "Average" rating
3. The "Poor" test is easiest - Is the product score below 60?
   • Products that weren't in the top two categories will automatically fall into the "Poor" rating

Formula: Harder Logical Tests First

To build the nested IF formula, with logical tests that have different levels of difficulty, it is important to put the hardest tests first, in order of difficulty.

Here is the sample data, with this formula in cell C7:

• =IF(B2>=80,"Good",IF(B2>=60,"Average","Poor"))

The score in cell B2 is 86, which passes the first logical test, so the result is "Good".

Wrong Result: Easier Test First

However, if the "Average" test is first in the formula, the result would be incorrect.

• =IF(B2>=60,"Average",IF(B2>=80,"Good","Poor"))

The score in cell B2 is 86, which passes the first logical test, so the result is "Average".

Notes on Nested IF Formulas

Here are a few important things to keep in mind, when building a nested IF formula:

1. When building nested IF formulas, the order in which the tests are listed might be important. See the section below -- the hardest tests must come first.
2. Don't nest too many IF functions in a formula, or the formula will be difficult to build correctly, and hard to maintain over time.
3. Many nested IF formulas can be replaced by a other solutions, such as a VLOOKUP formula, or INDEX and MATCH, for more flexibility. See the example in the "Is IF Function Best Solution?" section, below.

Ex 3: IF Function With Check Box

In this example, sales tax will be applied to local orders. On the order form, there is a check box that is linked to cell G11.

• For local orders, click the check box, to add a check mark, and the linked cell shows TRUE
• If the check mark is removed, the linked cell shows FALSE

The IF formula in cell E12 checks the linked cell (G11)l, to see if it contains "FALSE". If so, the tax amount is zero.

If cell G11 does not contain "FALSE", the subtotal in cell E10 is multiplied by the Tax Rate in cell D12, to show the tax amount.

=IF(G11=FALSE,0,E10*D12)

IFS Function - How It Works

In Excel 365, there is also an IFS function, which can be used instead of multiple nested IF functions. With the IFS function:

• You can test multiple conditions, to see if they are TRUE.
• The formula returns the result for the first TRUE condition.

IFS Function Syntax

The IFS function has the following arguments in its syntax:

1. logical_test1: TEST something, such as the value in a cell.
2. value_if_true1: Specify what should happen if the test result is TRUE.
3. logical_test2...logical test127: (optional) TEST something, such as the value in a cell.
4. value_if_true2...value_if_true127: (Optional) Specify what should happen if the test result is TRUE.

Note: Although you can enter up to 127 tests and their related values, Microsoft advises against nesting too many conditions. Limit the number of logical tests to just a few, or it will be difficult to build and maintain the IFS formula.

IFS Ex 1: Check for Empty Cells

In this example, instead of using a nested IF formula, here's an IFS formula.

In cell E9, the following IFS formula checks 3 cells in an order form, to see if the product name, price and quantity have been entered.

• =IFS(COUNTA(B9:D9)=3, C9*D9, B9="","", C9="","no price", D9="","no qty")

Here's how this IFS formula works:

1. First, the COUNTA function counts the values in cells B9:D9. Is that count equal to 3?
• If TRUE, multiply the price cell value (C9) by the quantity (D9), to calculate the total price
2. Next, cell B9 is checked, to see if it is empty. The two double quotes represent an empty string
• If TRUE, return an empty string
3. Then, cell C9 is checked, to see if it is empty
• If TRUE, return the text string "no price"
4. Finally, cell D9 is checked, to see if it is empty
   • If TRUE, return the text string "no price"

Below, you can see the results for this formula, in cells E9:E12

• In cell E9, the result is the total price, because all 3 cells are filled.
• In cell E10, the function returns an empty string, "", because the product name is missing
• In cell E11, the result is "no price", because the price cell is blank.

IFS Ex2: No value_if_false Argument

In Excel 365, you can use the IFS function, instead of multiple nested IF functions:

• test multiple conditions, to see if they are TRUE
• return result for the first TRUE condition

However, unlike the IF function, the IFS function does not have a final argument, for value_if_false

Without that argument, how can IFS formulas do the following?

• test 1, value_if_true1,
• test 2, value_if_true2,
• value_for_everything_else

Final Test and Value

As a workaround, to simulate the IF function's value_if_false argument:

• Use TRUE as the final logical test
• Use your "value for everything else" as the final "value_if_true" argument

For example, this formula has 2 logical tests, for Good and Average.

Any other scores, that don't pass the first two tests, would get a rating of "Poor", because they pass the final test

=IFS(
C9>=80,"Good",
C9>=60,"Average",
TRUE,"Poor"
)

Is IF Function the Best Solution?

Question: If you need to test if one or more logical conditions are TRUE or FALSE, then show a result, is the IF function, or the IFS function, always the best solution to use?

Answer: No, not usually. If you need to do anything more than a simple True or False test, other Excel functions provide a more efficient solution to the problem.

Example: Rating System

Here is one example, where a VLOOKUP function is easier to set up and maintain, compared to a nested IF function formula, or an IFS function formula (in Excel 365).

In some workbooks, you might have a rating system, so you can change a number score to a letter grade. In the example shown below, if a product is tested, and gets a score of 85 or higher, it gets 3 stars -- the highest rating.

IF Function and IFS Function Solutions

This IF formula, in cell D2, could calculate the rating:

• =IF(C2>=80,"***",IF(C2>=60,"**","*"))

Or, in Excel 365, this IFS formula could calculate the rating:

• =IFS(C2>=80,"***",C2>=60,"**",C2>=0,"*")

However, in both of those formulas, the rating levels are hard-coded into the formula. That could cause problems in the future, if the rating levels are ever changed.

• You would have to find all those formulas, and change all the rating level numbers.
• Or, if more rating levels were added, the formulas would have to be re-written.

VLOOKUP Function Solution

Instead of using IF or IFS, you could set up a rating lookup table, with the lowest score for each rating, and the rating group.

Then, use this VLOOKUP formula, in cell D2, to calculate the rating:

• =VLOOKUP(C2,RatingLookup,2,TRUE)

In the future, if the rating levels are ever changed, just change the information in the RatingLookup table.

• You would NOT have to find all those formulas, and change all the rating level numbers.
• Or, if more rating levels were added, the formulas would NOT have to be re-written.

More Examples

Here are a few more examples, where other formulas are more efficient than a nested IF function formula, or an IFS formula.

1. Convert student grades from numbers to letters -- use VLOOKUP or INDEX/MATCH
2. Choose an interest rate, based on transaction date -- use HLOOKUP
3. Show a specific value instead of an error -- use IFERROR (Excel 2007 and later)

Formulas in Named Excel Table

If your records are stored in a named Excel Table, you can use structured references in your formulas, instead of normal cell references.

For example, the following formula is in cell E7, to calculate the total price in that row:

• =[@Price] * [@Qty]

This formula uses the table's column names, like [Price], instead of normal cell references, like C7.

Normal Cell Reference and Structured Reference

Here are a few notes on normal and structured references:

• Normal cell reference has the cell address, such as C7
  • The reference might include one or two $ signs, such as $C7, C$7, $C$7
  • The $ sign creates an absolute reference to the row or column (will not change if formula is copied to another location)
  • Without the $ sign, the row or column reference is relative (can change if the formula is copied to another location)
• Structured reference has the field name inside square brackets, such as [Price] or [Qty]
  • Structured references may also include the table name, or an @ symbol, such as Table1[@Total]

Get the Sample File

To follow the examples in this tutorial, download the sample IF and IFS workbook. The zipped file is in xlsx format, and does not contain any macros.

More Tutorials

INDEX / MATCH

VLOOKUP

IFERROR

Which Excel function returns one value if a specific set of conditions is true and another value if the specific set of conditions is false?

Which Excel function returns a true value if all conditions are true?

Which function in Excel determines what happens if a condition is true or false?

What is an IF statement in Excel?