if formula assignment in excel

Excel IF Function

The IF function (or "IF statement") in Excel is a simple function that allows you to build logic into your worksheets. The IF function evaluates a logical test and returns one value if the logical test condition is true, and another value if the condition is false. Learning this function will help you begin to write complex logic to power your spreadsheets and models. For example, if a cell in your spreadsheet contains the top speed of a vehicle, you might write an IF statement which returns the text "fast" if the speed is above 30, and returns the text "slow" if the speed if below 30.

Syntax of the IF Function

The IF function takes three arguments (function inputs inside the parenthesis separated by commas):

=IF(condition, value_if_true, [value_if_false])

• condition : This is a statement or expression, usually comparing two values in the form of a logical comparison that evaluates to true or false. A logical comparison requires one of the logical operators in the table below.
• value_if_true : This is the value that will display if the condition is true.
• value_if_false : This optional argument is the value that the function will display if the condition argument is false.

The value_if_true and value_if_false arguments can be any type of values, including cell references, numbers, text, dates, as well as other functions or formulas.

Logical Statements in Excel

You may be wondering how to write a logical comparison for the first part of your IF statement. To compare two values, use one of the six logical comparison operators from the table below.

You can use these comparison operators to compare text values, dates, numbers, and more.

See the table below for some examples of logical comparisons in Excel.

Using the IF Function in Excel

Recall the syntax of an IF statement:

=IF( condition , value_if_true , value_if_false )

The first argument is the condition, or the logical test that compares two different values. If the condition is true, Excel returns the second argument (shown above as value_if_true). If the condition is false, Excel returns the third argument (shown above as value_if_false). See the example below:

=IF( C2>30 , "Fast" , "Slow" )

The IF statement above compares the value in cell C2 to 30. Excel returns the text "Fast" if speed is greater than 30 (because the condition is true), and returns the text "Slow" if speed is NOT greater than 30 (because the condition is false).

You could also omit the third argument:

=IF( C2>30 , "Fast" )

This IF statement would display the word "Fast" if speed was greater than 30, and would otherwise simply be blank. This is because the IF statement does not have the value_if_false argument.

Combining IF With Other Excel Functions

You saw how an IF statement can return text values, but we can also combine any other function in Excel with any of the arguments of an IF statement. For example, take this exercise using the SUM function : IF Texas and California sales combined are at least $500, commission is 10% on the total. Otherwise Commission is 0.

=IF( SUM(C2, C3)>=500 , SUM(C2, C3)*0.1 , 0 )

The formula above says If Sales for TX + CA is greater than or equal to 500, then return 10% of the total. Otherwise, return 0.

Because the SUM function in the condition would return 865, which is greater than 500, our IF statement would return the value_if_true argument. The value_if_true argument returns the sum multiplied by 0.1, so this IF statement would return 86.5.

Nested IF Statements

You can combine any number of IF Statements in Excel by "nesting" them inside the second or third arguments of another IF statement. For example, say you are shopping for a new car. You want a newer car, but you also like older Cadillacs. You decide to look for a car that meets the following conditions:

• If the car is a Cadillac, buy it.
• If the car is not a Cadillac, buy it only if it was made after the year 2010.
• If the car is not a Cadillac and was made in 2010 or earlier, do not buy.

Below we see different car options in a table. We can write a nested IF statement, starting with row 2, to help you determine whether to buy the car or not.

=IF( C2="Cadillac" , "Buy" , IF(B2>2010, "Buy", "Do not buy") )

The IF statement above says If make is equal to "Cadillac", then buy it. Otherwise, if the year is greater than 2010, then buy it. Otherwise do not buy it.

Starting with row 2, the make is not Cadillac, so we then go to the value_if_false clause. The year is not greater than 2010, so we would not buy the car.

For row 3, the make is Cadillac, so we would buy the car.

In row 4, the make is not Cadillac so we go to the value_if_false clause again. This time the year is greater than 2010, so we would buy this car.

Using IF Functions with AND and OR Functions in Excel

Say you want your IF statement to trigger the value_if_true_ argument only if several different conditions are all true. The AND function takes comparisons as its arguments and returns TRUE if ALL of the arguments are true. For example, the function =AND(A1>B1, A2=B2, A3<=B3) will return TRUE only if ALL three arguments are true. It will return FALSE if any of its arguments is false.

Now imagine you want your IF statement to trigger the value_if_true_ argument if at least one of several different conditions is true. The OR function also takes comparisons as its arguments and returns TRUE if AT LEAST ONE of the arguments is true. For example, the function =OR(A1>B1, A2=B2, A3<=B3) will return TRUE if AT LEAST ONE of the three arguments is true. It will return FALSE only if ALL of the arguments are false.

To use AND or OR with an IF function, you simply replace the condition argument with the AND or OR function:

=IF( AND(condition_1, [condition_2]...) ,  value_if_true ,  value_if_false )

=if( or(condition_1, [condition_2]...) ,  value_if_true ,  value_if_false ), continue to if practice exercises →.

How-To Geek

How to use the if function in microsoft excel.

Your changes have been saved

Email is sent

Email has already been sent

Please verify your email address.

You’ve reached your account maximum for followed topics.

Quick Links

What can you do with excel's if function, how to write an if statement in excel, use the nested if function in excel, key takeaways.

The IF function returns different values depending on whether a condition is true or false. Use it in the form =IF(Condition,True,False). For example, =IF(C2>=60,"Pass","Fail") will return "Pass" if the value in C2 is equal to or over 60 and "Fail" if the value is under 60.

Whether you're grading exams or simply trying to make sense of a spreadsheet full of data, Microsoft Excel's IF function can help. You can also use an IF function inside of another IF function to run deeper tests, too. We'll show you how.

Put simply, you can use the IF function to retrieve a pre-specified result based on whether the function gets a TRUE or FALSE value.

For example, if you have a score sheet, you can make it so your cells say

if someone has scored 60 or higher, or say

if the score is 59 or lower. You can use a nested IF to even assign grades, like an

for someone with a score of 90 or higher.

To write an IF statement in Excel, all you have to do is type the function and specify what results to retrieve when the condition is TRUE and FALSE.

Related: How to Use Conditional Formatting to Find Duplicate Data in Excel

Start by launching your spreadsheet with Microsoft Excel. Then, click the cell in which you want to use the function.

In the following example, we'll use the IF function to say

if the obtained score is 60 or higher and

if the score is 59 or lower.

We'll select the D2 cell where we want to display the result.

In the D2 cell, we'll enter the following function and press Enter.

=IF(C2>=60,"Pass","Fail")

In the selected cell, you'll see the result depending on the value in the C2 cell.

To copy the function for all your records, from the bottom-right corner of the D2 cell, drag downwards to cover all your records.

And that's it.

Modify the IF function in whatever way you want and you'll get the desired result.

A nested IF is an IF function inside of another IF function. You use this when you want to run another logical test after the first one.

We'll use the following dataset to demonstrate this function:

In this dataset, depending on the scores, the following results will be displayed:

• If the score is 90 or higher: A
• If the score is between 80 and 89: B
• If the score is between 70 and 79: C
• If the score is between 60 and 69: D
• If the score is between 0 and 59: F

We'll select the D2 cell where we want to display the result, and then enter the following nested IF function and press Enter:

=IF(C2>=90,"A",IF(C2>=80,"B",IF(C2>=70,"C",IF(C2>=60,"D",IF(C2>=0,"F")))))

You'll see the result in your selected cell.

You can copy the function for all your records by dragging downwards from the D2 cell's bottom-right corner.

And you're set.

Excel's IF function is an excellent way to run various logical tests. You can use it to specify multiple conditions and display the results accordingly.

While you're at it, check out other Excel logical functions that can be useful in your work.

• Microsoft Office
• Microsoft Excel

How to Use the IF Function in Excel: Formulas and Examples

Would you like to make your spreadsheets smarter? Excel’s IF function is an easy way to add logic and decision-making power. Learn how to use this versatile function to calculate discounts, allowances, or commissions based on different conditions or values in your data. This step-by-step guide includes a downloadable practice file.

Knowledge You’ll Gain:

• Understand the core concepts of the IF function and how it works in Excel.
• Confidently build basic IF formulas to make logical decisions in your spreadsheets.
• Apply the IF function to various scenarios, such as calculating allowances or partial payments
• Use the IF function with different data types, including text, numbers, and dates.
• Download and use the practice file to reinforce your learning.

Understanding the Excel IF Function

The IF function is one of the most straightforward and useful logical functions. It can fill cell items based on evaluating a condition, such as a cell’s content and logical operators. What’s appealing about the function is that it can be used with other functions to handle more complex scenarios. You might think of it as a formula building block, and you can find it in the Logical category.

The wizard-like dialog allows you to fill 3 Function Arguments or data elements, but you could use the formula bar once you master it. This is the easiest way to learn an Excel formula because you can see if it returns your expected result. For example, a line at the bottom left of the dialog reads “ Formula result = .”

IF Function Syntax and Arguments

Despite not having Microsoft Excel, my parents routinely employed this type of IF logic when calculating my allowance. Their version read:

IF you empty the garbage, mow the lawn, wash the dishes, AND walk the dog , you get your total allowance .

And since I grew up in New England, this logic would change with the seasons to account for leaves and snow.

Constructing Your First IF Function

Although Excel can’t issue an allowance, it can calculate the correct amount using a logic test based on whether a cell met a formula condition.

For example, I could create a spreadsheet with the Task needed to get an allowance. A value would be applied toward the allowance if the Tasks were completed (TRUE situation). Nothing would be added if the Task wasn’t completed (FALSE situation).

These examples are noted by blue square labels [1] and [2] in the image below.

Using the example above, you might express the logic in the following way:

IF cell B2 = Y , then use the Rate value from cell C2 ($3.00) in D2 .

IF cell B2 ≠ Y , then place $0.00 in cell D2 .

As you can see in this example, the IF logical condition is TRUE or FALSE . And it pays to take out the garbage.

IF Function: Comparison Operators & Syntax

To help evaluate conditions, Excel uses a list of familiar operators. You probably know these as we probably used them in math class. These operators will be evaluated as logical “true” or “false”. In the table below, B2 and C2 in the Example column are cell references.

Entering IF Function Arguments (Step-by-Step)

• Click the spreadsheet cell where you wish to use the Excel formula.
• From the Formulas tab, click Insert function…
• In the Insert Function dialog text box, type “ if “.

On Office 365, there is now a Logical button on the Formulas tab. You can select IF from the drop-down menu.

• Make sure your cursor is in the Logical_test text box.
• Click the spreadsheet cell you wish to evaluate. Excel will fill in the cell reference such as “B2”.
• Add the equals sign = and your desired value in quotes . For example =”Y”.
• In the Value_if_true field, type the value you want to be entered in your cell if B2 equals “Y”. In our example, I’ll click cell C3.
• In the Value_if_false : field, enter the value the cell should have if B2 does not have a “Y”. I’ll enter 0. I could leave it blank, but the cell would show “FALSE”.
• Review the dialog to see if the Formula result= value (label [1] below) is what you expect. If not, check to see if any errors show to the right of the fields (label [2] below).

• Copy the formula to the other cells in your column.

Even though the Value_if_false field is optional, providing a value is best. Otherwise, Excel will use FALSE in the cell value.

IF Function With Numeric Values (Formula Example)

The above spreadsheet might have been Version 1 for my parents. A new incentive program would appear based on some parent/child negotiations and competitive neighborhood rates. I probably would’ve requested pay for partial chores. No doubt, my parents would counter with a penalty clause if something was less than half done.

Excel is flexible when it comes to IF statements and can evaluate more than a simple “Y” or “N.” For example, if we convert our previous Done? column to a % Done column with a number, we can accommodate these new requirements such as:

The new formula returns the allowance based on the % Done in Column B. If the chore completion number is greater than .5, a prorated amount is applied to the allowance. 

A negative amount was applied to the allowance if the chore completion rate was .5 or below. Loosely translated, an “incomplete” performance costs money. You could also apply colors using conditional formatting .

Troubleshooting Excel IF Formula Errors

The IF function doesn’t always work as expected for several reasons.

• You don’t have quotes around a text string . For example, you used B2=Y instead of B2=”Y”. The quotes aren’t needed if you use the values TRUE or FALSE as Excel recognizes them.
• You have a data type mismatch . For example, your comparison operator was comparing a number in one cell, such as “twelve”, but the comparison cell had 12. However, Excel is forgiving if you evaluate number formats such as 1.5 versus $1.50.

As you’ve seen, this is a versatile and foundational function. Once you know how to use the IF function in Excel, you’ll use it in more scenarios. The two examples presented here were foundational. However, you can use IF functions to handle other transactions, such as sales tax, stock values, shipping charges, or  fixing Excel DIV 0 errors . 

Download the practice file below and experiment with the IF function using your data. With a little practice, you’ll be able to confidently apply this powerful tool in various scenarios and unlock the full potential of your spreadsheets. And if you have kids, why not let them build the Excel spreadsheet and give them a bonus for using the function.

Related Excel Resource

Hand-picked excel tutorials.

• How to Use Excel VLOOKUP
• Beginner’s Guide to XLOOKUP
• How to Create an Excel Pivot Table
• The Benefits of Creating Excel Tables
• Video: How to Use Conditional Formatting

How to use IF function in Excel

This is a step-by-step guide on how to use IF function in Excel. It shows you how to create a formula using the IF function, it includes several IF formula examples, an introduction to how to use nested IF formulas, and the exercise file I used when creating this tutorial.

How do you write an if-then formula in Excel? Well, the syntax for IF statements is the same in all Excel versions . This means that you can use any of the examples shown in this article in Excel for Microsoft 365 or Excel 2021, 2019, 2016, 2013, 2010, 2007, and 2003.

The syntax that shows how to create an IF function in Excel is explained below: =IF(logical_test, [value_if_true], [value_if_false])

Please remember that while both value_if_true and value_if_false are optional, at least one of them needs to be supplied. Otherwise, your IF formula will simply return 0 (zero).

Logical operators for IF function

Your first IF formula

The IF function runs a logical test and returns different values depending on whether the result is TRUE or FALSE. The result from IF can be a value, a cell reference, or even another formula.

The 3 arguments for this IF formula are: logical_test : E9>=70 value_if_true : Pass is returned if E9>=70. value_if_false : Fail is returned if E9<70.

Please note that when you want to use text in your IF formulas (like a word or sentence), you need to wrap the text in quotes (e.g. “Fail”). The only exception is while using TRUE or FALSE, which are built-in functionalities that Excel recognizes automatically.

How to use the IF function in Excel with another function or formula

In our next example, we will use the IF function to calculate a payment fee based on the value of the order. If the order value is higher than or equal to $1000, then it should calculate a payment fee of 1.00%. However, if the total order value is lower than $1000, then it should use 1.50%.

Now let’s look at an IF formula that is dependent on user input. If we select free shipping for the order, then the shipping fee will be set to zero. Otherwise, it will be calculated as 3% of the order value.

How to use nested IF statements in Excel

Nesting more IF functions allows you to perform multiple comparisons and create more complex formulas. However, you can only nest up to 64 IF functions in Excel. If you ever reach this limit (I never did), I can guarantee that there is a better and more elegant solution using functions like VLOOKUP , SUMIF , or COUNTIFS .

Note: If you are running Office 365, then you can also look at the new IFS function. This function runs multiple tests and returns the value corresponding to the first TRUE result. It’s a very useful alternative to nested IF formulas and makes your formulas much easier to understand by others. You can read more about IFS on Microsoft’s website .

How to use IF formula with OR function in Excel

The IF OR statement I’ve used in cell H106 is: =IF(OR(G106>1000, F106>5), G106*2%, 0)

How to use IF formula with AND function in Excel

Working with our previous example, let’s apply the shipping fee only when the total order value is higher than $1000 and the order contains more than 5 items.

How to use IF function with VLOOKUP in Excel

VLOOKUP can be nested inside an IF formula to retrieve data when a condition is TRUE or FALSE. In the next example, I will show you how to calculate shipping fees based on a table containing the thresholds and percentages to be applied depending on the order value.

Note: Remember that I’ve used a VLOOKUP formula with an approximate match argument. This means that your data must be sorted in ascending order by lookup value (in our case, the Order amount).

What to do next?

Let me know if you have questions on how to use IF function in Excel or if you need advice on how to nest multiple IF statements in your Excel project by leaving a comment below.

I have numerics in J187 and K187, Date in L187. M187 is the result. I want M187=J187 if K187>0,ELSE 0. I also want M187=0, if K187>0 AND L187 is a valid date. I have tried the following formula in Google Sheet, but results are not always correct: =IF(AND(K187>0,L1870,L187>0),0)) Kindly help if possible

Sure, just adjust the cells from this formula and you should be good:

HELP ME TO SOLVE THIS PLEASE =IF(H696.5%=100. How can i formila please healp thank you.

=IF(OR(A1=”Mon”, A1=”Tue”, A1=”Wed”, A1=”Thu”, A1=”Fri”), 9.5, IF(A1=”Sat”, 4, IF(OR(A1=”Sun”, A1=”PH”), 12)))

If you are not using Office 2021 of Office 365, you will need to finish the formula with CTRL + SHIFT + ENTER to convert it to an array formula.

Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.

Return to List of Excel Functions

IF Formula – If Then Statements – Excel & Google Sheets

Editorial Team

Reviewed by

Steve Rynearson

Download the example workbook

This tutorial demonstrates how to use the IF Function in Excel and Google Sheets to create If Then Statements.

IF Function Overview

The IF Function Checks whether a condition is met. If TRUE do one thing, if FALSE do another.

How to Use the IF Function

Here’s a very basic example so you can see what I mean. Try typing the following into Excel:

Since 2 + 2 does in fact equal 4, Excel will return “It’s true!”. If we used this:

Now Excel will return “It’s false!”, because 2 + 2 does not equal 5.

Here’s how you might use the IF statement in a spreadsheet.

You run a sports bar and you set individual tab limits for different customers. You’ve set up this spreadsheet to check if each customer is over their limit, in which case you’ll cut them off until they pay their tab.

You check if C4-D4 (their current tab amount minus their limit), is greater than 0. This is your logical test. If this is true, IF returns “Yes” – you should cut them off. If this is false, IF returns “No” – you let them keep drinking.

What can IF Return?

Above we returned a text string, “Yes” or “No”. But you can also return numbers, or even other formulas.

Let’s say some of your customers are running up big tabs. To discourage this, you’re going to start charging interest on customers who go over their limit.

You can use IF for that:

If the tab is higher than the limit, return the tab multiplied by 0.03, which returns 3% of the tab. Otherwise, return 0: they aren’t over their tab, so you won’t charge interest.

Using IF with AND

You can combine IF with Excel’s AND Function to test more than one condition. Excel will only return TRUE if ALL of the tests are true.

So, you implemented your interest rate. But some of your regulars are complaining. They’ve always paid their tabs in the past, why are you cracking down on them now? You come up with a solution: you won’t charge interest to certain trusted customers.

You make a new column to your spreadsheet to identify trusted customers, and update your IF statement with an AND function:

Let’s look at the AND part separately:

Note the two conditions:

• C4>D4: checking if they’re over their tab limit, as before
• F4=”No”: this is the new bit, checking if they are not a trusted customer

So now we only return the interest rate if the customer is over their tab, AND we have “No” in the trusted customer column. Your regulars are happy again.

Using IF with OR

The OR Function allows you to test more than one condition, returning TRUE if any conditions are met .

Maybe customers being over their tab is not the only reason you’d cut them off. Maybe you give some people a temporary ban for other reasons, gambling on the premises perhaps.

So you add a new column to identify banned customers, and update your “Cut off?” column with an OR test:

Looking just at the OR part:

There are two conditions:

• C4>D4: checking if they’re over their tab limit
• F4=”Yes”: the new part, checking if they are currently banned

This will evaluate to true if they are over their tab, or if there is a “Yes” in column E. As you can see, Harry is cut off now, even though he’s not over his tab limit.

Using IF with XOR

The XOR Function returns TRUE if only one condition is met. If more than one condition is met (or not conditions are met). It returns FALSE.

An example might make this clearer. Imagine you want to start giving monthly bonuses to your staff :

• If they sell over $800 in food, or over $800 in drinks, you’ll give them a half bonus
• If they sell over $800 in both, you’ll give them a full bonus
• If they sell under $800 in both, they don’t get any bonus.

You already know how to work out if they get the full bonus. You’d just use IF with AND, as described earlier.

But how would you work out who gets the half bonus? That’s where XOR comes in:

As you can see, Woody’s drink sales were over $800, but not food sales. So he gets the half bonus. The reverse is true for Coach. Diane and Carla sold more than $800 for both, so they don’t get a half bonus (both arguments are TRUE), and Rebecca made under the threshold for both (both arguments FALSE), so the formula again returns “No”.

Using IF with NOT

The NOT Function reverses the outcome of a logical test. In other words, it checks whether a condition has not been met.

You can use it with IF like this:

Here we have a table with data on some 1980s movies. We want to identify movies released on or after 1985, that were not directed by Steven Spielberg.

Because NOT is nested within an AND Function, Excel will evaluate that first. It will then use the result as part of the AND.

Nested IF Statements

You can also return an IF statement within your IF statement. This enables you to make more complex calculations.

Let’s go back to our customers table. Imagine you want to classify customers based on their debt level to you:

• Up to $500: Low
• $500 to $1000: Medium
• Over $1000: High

You can do this by “nesting” IF statements:

It’s easier to understand if you put the IF statements on separate lines ( ALT + ENTER on Windows, CTRL + COMMAND + ENTER on Macs):

IF C4 is 0, we return “None”. Otherwise, we move to the next IF statement. IF C4 is equal to or less than 500, we return “Low”. Otherwise, we move on to the next IF statement… and so on.

Simplifying Complex IF Statements with Helper Columns

If you have multiple nested IF statements, and you’re throwing in logic functions too, your formulas can become very hard to read, test, and update.

This is especially important to keep in mind if other people will be using the spreadsheet. What makes sense in your head, might not be so obvious to others.

Helper columns are a great way around this issue.

You’re an analyst in the finance department of a large corporation. You’ve been asked to create a spreadsheet that checks whether each employee is eligible for the company pension.

Here’s the criteria:

So if you’re under the age of 55, you need to have 30 years’ service under your belt to be eligible. If you’re aged 55 to 59, you need 15 years’ service. And so on, up to age 65, where you’re eligible no matter how long you’ve worked there.

You could use a single, complex IF statement to solve this problem:

Whew! Kinda hard to get your head around that, isn’t it?

A better approach might be to use helper columns. We have five logical tests here, corresponding to each row in the criteria table. This is easier to see if we add line breaks to the formula, as we discussed earlier:

So, we can split these five tests into separate columns, and then simply check whether any one of them is true:

Each column in the table from E to I holds each of our criteria separately. Then in J4 we have the following formula:

Here we have an IF statement, and the logical test uses COUNTIF to count the number of cells within E4:I4 that contain TRUE.

If COUNTIF doesn’t find a TRUE value, it will return 0, which IF interprets as FALSE, so the IF returns “Not Eligible”.

If COUNTIF does find any TRUE values, it will return the number of them. IF interprets any number other than 0 as TRUE, so it returns “Eligible”.

Splitting out the logical tests in this way makes the formula easier to read, and if something’s going wrong with it, it’s much easier to spot where the mistake is.

Using Grouping to Hide Helper Columns

Helper columns make the formula easier to manage, but once you’ve got them in place and you know they are working correctly, they often just take up space on your spreadsheet without adding any useful information.

You could hide the columns, but this can lead to problems because hidden columns are hard to detect, unless you look closely at the column headers.

A better option is grouping.

Select the columns you want to group, in our case E:I. Then press ALT + SHIFT + RIGHT ARROW on Windows, or COMMAND + SHIFT + K on Mac. You can also go to the “Data” tab on the ribbon and select “Group” from the “Outline” section .

You’ll see the group displayed above the column headers, like this:

Then simply press the “-“ button to hide the columns:

The IFS Function

Nested IF statements are very useful when you need to perform more complex logical comparisons, and you need to do it in one cell. However, they can get complicated as they get longer, and they can be hard to read and update on your screen.

From Excel 2019 and Excel 365, Microsoft introduced another function, the IFS Function , to help make this a bit easier to manage. The nested IF example above could be achieved with IFS like this:

You can read all about it on the main page for the Excel IFS Function <<link>>.

Using IF with Conditional Formatting

Excel’s Conditional Formatting feature enables you to format a cell in different ways depending on its contents. Since the IF returns different values based on our logical test, we might want to use Conditional Formatting with the IF Function to make these different values easier to see.

So let’s go back to our staff bonus table from earlier.

We’re returning “Yes” or “No” depending on what bonus we want to give. This tells us what we need to know, but the information doesn’t jump out at us. Let’s try to fix that.

Here’s how you’d do it:

• Select the cell range containing your IF statements. In our case that’s E4:F8.
• Click “Conditional Formatting” on the “Styles” section of the “Home” tab on the ribbon.
• Click “Highlight Cells Rules” and then “Equal to”.
• Type “Yes” (or whatever return value you need) into the first box, and then choose the formatting you want from the second box. (I’ll choose green for this).
• Repeat for all your return values (I’ll also set “No” values to red)

Here’s the result:

Using IF in Array Formulas

An array is a range of values, and in Excel arrays are represented as comma separated values enclosed in braces, such as:

The beauty of arrays, is that they enable you to perform a calculation on each value in the range, and then return the result. For example, the SUMPRODUCT Function takes two arrays, multiplies them together, and sums the results.

So this formula:

…returns 32. Why? Let’s work it through:

We can bring an IF statement into this picture, so that each of these multiplications only happens if a logical test returns true.

For example, take this data:

If you wanted to calculate the total commission for each sales manager, you’d use the following:

Note: In Excel 2019 and earlier, you have to press CTRL + SHIFT + ENTER to turn this into an array formula.

We’d end up with something like this:

Breaking this down, the “Manager” column is column C, and in this example, Olivia’s name is in G2.

So the logical test is:

In English, if the name in column C is equal to what’s in G2 (“Olivia”), DO multiply the values in columns D and E for that row. Otherwise, don’t multiply them. Then, sum all the results.

You can learn more about this formula on the main page for the SUMPRODUCT IF Formula .

IF  in Google Sheets

The IF Function works exactly the same in Google Sheets as in Excel:

VBA IF Statements

You can also use If Statements in VBA . Click the link to learn more, but here is a simple example:

This code will test if a cell value is negative. If so, it will write “negative” in the next cell.

AI Formula Generator

Try for Free

• Excel Help Bot
• Formula Generator
• Formula Explainer
• VBA Code Generator
• VBA Code Commenter
• Excel Template Generator

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

• Automatically Graded Exercises
• Learn Excel, Inside Excel!

Free Download

IF Function

Tests a given condition and returns one value for a TRUE result, and another for a FALSE result

What is an Excel IF Statement?

The Excel IF Statement tests a given condition and returns one value for a TRUE result and another value for a FALSE result. For example, if sales total more than $5,000, then return a “Yes” for Bonus; otherwise, return a “No” for Bonus. We can also use the IF function to evaluate a single function, or we can include several IF functions in one formula. Multiple IF statements in Excel are known as nested IF statements.

As a financial analyst , the IF function is used often to evaluate and analyze data by evaluating specific conditions.

The function can be used to evaluate text, values, and even errors. It is not limited to only checking if one thing is equal to another and returning a single result. We can also use mathematical operators and perform additional calculations, depending on our criteria. We can also nest multiple IF functions together to perform multiple comparisons.

=IF(logical_test, value_if_true, value_if_false)

The function uses the following arguments:

• Logical _ test (required argument) – This is the condition to be tested and evaluated as either TRUE or FALSE.
• Value _ if_true (optional argument) – The value that will be returned if the logical_test evaluates to TRUE.
• Value_if_false (optional argument) – The value that will be returned if the logical_test evaluates to FALSE.

When using the IF function to construct a test, we can use the following logical operators:

• = (equal to)
• > (greater than)
• >= (greater than or equal to)
• < (less than)
• <= (less than or equal to)
• <> (not equal to)

How to use the Excel IF Function

To understand the uses of the Excel IF statement function, let’s consider a few examples:

Example 1 – Simple Excel IF Statement

Suppose we wish to do a very simple test. We want to test if the value in cell C2 is greater than or equal to the value in cell D2. If the argument is true, then we want to return some text stating “Yes it is”, and if it’s not true, then we want to display “No it isn’t”.

You can see exactly how the Excel IF statement works in the simple example below.

Result when true:

Result when false:

Example 2 – Excel IF Statement

Suppose we wish to test a cell and ensure that an action is taken if the cell is not blank. We are given the data below:

In the worksheet above, we listed AGM-related tasks in Column B. Remarks contain the date of completion. In Column C, we will use a formula to check if the cells in Column D are empty or not. If a cell is blank, the formula will assign the status “Open.” However, if a cell contains a date, then the formula will assign a status of “Closed.” The formula used is:

We get the results below:

Example 3 – Excel IF Statement

Sellers frequently provide a discount based on the quantity purchased. Suppose we are given the following data:

Using multiple IF functions, we can create a formula to check multiple conditions and perform different calculations depending on what amount range the specified quantity falls in. To calculate the total price for 100 items, the formula will be:

We get the result below:

Things to remember about the IF Function

• The Excel IF function will work if the logical_test returns a numeric value. In such a case, any non-zero value is treated as TRUE and zero is treated as FALSE.
• #VALUE! error – Occurs when the given logical_test argument cannot be evaluated as TRUE or FALSE.
• When any of the arguments are provided to the function as arrays, the IF function will evaluate every element of the array.
• If we wish to count conditions, we should use the COUNTIF and COUNTIFS functions.
• If we wish to add up conditions, we should use the SUMIF and SUMIFS functions.

Reasons to use an Excel IF Statement

There are many reasons why an analyst or anyone who uses Excel would want to build IF formulas.

Common examples include:

• To test if an argument is true or false
• To output a NUMBER
• To output some TEXT
• To generate a conditional formula (e.g., the result is C3+B4 if true and N9-E5 if false)
• To create scenarios  to be used in financial modeling
• To calculate a debt schedule or a fixed asset depreciation schedule in accounting

Additional resources

Thanks for reading CFI’s guide on how to use an Excel IF Statement. By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:

• Free Excel Fundamentals Course
• Advanced Excel Formulas Guide
• Excel Shortcuts List
• IFNA Function
• See all Excel resources

Excel Tutorial

To master the art of Excel, check out CFI’s Excel Crash Course , which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

Launch CFI’s Excel Course now

to take your career to the next level and move up the ladder!

• Share this article

Create a free account to unlock this Template

Access and download collection of free Templates to help power your productivity and performance.

Already have an account? Log in

Supercharge your skills with Premium Templates

Take your learning and productivity to the next level with our Premium Templates.

Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.

Already have a Self-Study or Full-Immersion membership? Log in

Access Exclusive Templates

Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.

Already have a Full-Immersion membership? Log in

• Excel Courses
• Free Excel Courses
• Excel Video Tutorials
• Excel Keyboard Shortcuts

Premium Excel Course Now Available!

Build professional - unbreakable - forms in excel, 45 tutorials - 5+ hours - downloadable excel files, 15 if statement examples in excel, black friday sale (65%-80% off).

Excel Courses Online Video Lessons Excel Guides

VBA Course (80% Off)

Forms Course (65% Off)

Email Course (65% Off)

15 different examples of using the IF statement function in Microsoft Excel.

This includes examples that range from simple to complex and includes many different scenarios that will show you the ways that you can use the IF statement to make your spreadsheets better and more advanced.

You will learn how to use comparison operators, text, numbers, other functions and even other IF statements within IF statements.

Question? Ask it in our Excel Forum

Excel vba course - from beginner to expert.

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA Course - Beginner to Expert

Subscribe for weekly tutorials, bonus: subscribe now to download our top tutorials ebook.

The link to our top 15 tutorials has been sent to you, check your email to download it!

(If you don't see the email, check your Spam or Promotions folder and make sure to add us as a contact so you get our emails in the future.)

200+ Video Lessons 50+ Hours of Video 200+ Excel Guides

Related functions 

To test a condition, and take one action if the condition is TRUE, and another action if the condition is FALSE, you can use the IF function . In the example shown, the formula in cell E5 is:

As the formula is copied down, it returns "Small" when the value in column D is "S" and "Large" when the value in column D is "L". Note that text values within the formula must be enclosed in double quotes ("").

Generic formula

Explanation .

The goal is to return "Small" when the value in column D is "S" and "Large" when the value in column D is "L". In other words, if the value in column D is "S" return "Small" else return "Large".

If else in Excel

The concept of "If else" in Excel is handled with the IF function . The IF function runs a test, then returns one value if the result is TRUE, and a different value if the result is FALSE. The generic syntax for IF looks like this:

For example, to check cell A1 and return "Yes" if the value is greater than 100 and "No" if not, you can use the IF function like this:

Note that the "else" concept is built into the IF function. The first argument is the logical test, and the second argument is the result (or calculation) to return when the test is TRUE. The third argument is the "else" — the value or calculation to return if the result of the logical test is FALSE.

Example worksheet problem

In the worksheet shown, we have a list of T-shirts that includes color and size. The sizes in column D are abbreviated, with "S" for small and "L" for large. There are only these two sizes in the data. Let's say you want to write a formula to expand these abbreviations and show either the word "Small" or "Large" in column E. In other words:

• If a cell in column D contains "S", return "Small".
• If a cell in column D contains "L", return "Large".

This is a perfect application of the IF function. To check the abbreviated size in column D and return either "Small" or "Large", the formula in cell E5 is:

Translated, this means:  IF cell D5 equals "S", return "Small", ELSE return "Large".

Notice we are only testing for "S" — we don't need to test for "L". That's because we only have two possible values, and the ELSE part of the formula (the FALSE result) logically takes care of "L" for us: if the cell doesn't contain "S", it must be "L".

Nesting IFs  (if elseif)

As seen above, handling a single condition with the IF statement is simple. But how do you implement the idea of "If elseif" in Excel? The formula above works fine if we only have two sizes, Small ("S") and Large ("L"), but what if we have another size, "M" for "Medium"? In that case, we can extend the formula with another IF statement. We do this by replacing the existing FALSE result with a second IF function. In the example below, we've extended the formula to handle a medium size. The formula in E5 is:

Roughly translated, the formula now means: "If D5 is "S" return "Small", elseif D5 is "M" return "Medium", else return "Large".  This technique is called " nesting " since we are placing one function inside another. When more than one IF function is nested together in a formula, you will sometimes hear the formula called a "Nested IF formula" or "Nested IFs" for short. This page has many examples . 

Other options

It is possible to nest many IF functions together in a single formula. However, the longer a formula like this gets, the harder it is to read and maintain. Before you create a long nested IF formula, you should consider other options:

• The IFS function is designed to handle multiple options without nesting.
• The  VLOOKUP function  can handle many options with a  simple formula .

Related formulas

• If this AND that

• If cell is this OR that

• If NOT this or that

• If cell contains

• Nested IF function example

Related functions

• IF Function

The Excel IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical...

• IFS Function

The Excel IFS function can run multiple tests and return a value corresponding to the first TRUE result. Use the IFS function to evaluate multiple conditions without multiple nested IF statements. IFS allows shorter, easier to read formulas....

• VLOOKUP Function

The Excel VLOOKUP function is used to retrieve information from a table using a lookup value. The lookup values must appear in the first column of the table, and the information to retrieve is specified by column number . VLOOKUP supports approximate and exact matching...

Related videos

• The IF function

• If this OR that

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Related Information

• 19 tips for nested IF formulas
• Core Formula
• Dynamic Array Formulas

Get Training

Quick, clean, and to the point training.

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

Help us improve Exceljet

Your email address is private and not shared.

Multiple If Statements in Excel (Nested IFs, AND/OR) with Examples

We use the IF statement in Excel to test one condition and return one value if the condition is met and another if the condition is not met.

However, we use multiple or nested IF statements when evaluating numerous conditions in a specific order to return different results.

This tutorial shows four examples of using nested IF statements in Excel and gives five alternatives to using multiple IF statements in Excel .

Table of Contents

General Syntax of Nested IF Statements (Multiple IF Statements)

The general syntax for nested IF statements is as follows:

This formula tests the first condition; if true, it returns the first value.

If the first condition is false, the formula moves to the second condition and returns the second value if it’s true.

Each subsequent IF function is incorporated into the value_if_false argument of the previous IF function.

This process continues until all conditions have been evaluated, and the formula returns the final value if none of the conditions is true.

The maximum number of nested IF statements allowed in Excel is 64.

Now, look at the following four examples of how to use nested IF statements in Excel.

Example #1: Use Multiple IF Statements to Assign Letter Grades Based on Numeric Scores

Let’s consider the following dataset showing some students’ scores on a Math test. 

We want to use nested IF statements to assign student letter grades based on their scores. 

We use the following steps:

• Select cell C2 and type in the below formula:

• Click Enter in the cell to get the result of the formula in the cell.
• Copy the formula for the rest of the cells in the column

The assigned letter grades appear in column C.

Explanation of the formula

=IF(B2>=90,”A”,IF(B2>=80,”B”,IF(B2>=70,”C”,IF(B2>=60,”D”,”F”))))

This formula evaluates the value in cell B2 and assigns an “A” if the value is 90 or greater, a “B” if the value is between 80 and 89, a “C” if the value is between 70 and 79, a “D” if the value is between 60 and 69, and an “F” if the value is less than 60.

Notice that it can be challenging to keep track of which parentheses go with which arguments in nested IF functions.

Therefore, as we enter the formula, Excel uses different colors for the parentheses at each level of the nested IF functions to make it easier to see which parts of the formula belong together. 

Example #2: Use Multiple IF Statements to Calculate Commission Based on Sales Volume

Here’s the dataset showing the sales of specific salespeople in a particular month.

We want to use multiple IF statements to calculate the tiered commission for the salespeople based on their sales volume. 

We proceed as follows:

• Select cell C2 and enter the following formula:

• Press the Enter key to get the result of the formula.
• Double-click or drag the Fill Handle to copy the formula down the column.

The commission for each salesperson is displayed in column D.

=IF(B2>=40000, B2*0.14,IF(B2>=20000,B2*0.12,IF(B2>=10000,B2*0.105,IF(B2>0,B2*0.08,0))))

This formula evaluates the value in cell B2 and then does the following:

• If the value in cell B2 is greater than or equal to 40,000, the figure is multiplied by 14% (0.14).
• If the figure in cell B2 is less than 40,000 but greater than or equal to 20,000, the value is multiplied by 12% (0.12).
•  If the number in cell B2 is less than 20,000 but greater than or equal to 10,000, the figure is multiplied by 10.5% (0.105).
• If the value in cell B2 is less than 10,000 but greater than 0 (zero), the number is multiplied by 8% (0.08).
• If the value in cell B2 is 0 (zero), 0 (zero) is returned.

Example #3: Use Multiple IF Statements to Assign Sales Performance Rating Based On Sales Target Achievement

The following is a dataset showing regional sales data of a specific technology company in a particular year.

We want to use multiple IF statements to assign a sales performance rating to each region based on their sales target achievement.

• Click Enter on the Formula bar.
• Drag or double-click the Fill Handle to copy the formula down the column.

The performance ratings of the regions are shown in column C.

=IF(B2>500000, “Excellent”, IF(B2>400000, “Good”, IF(B2>275000, “Average”, “Poor”)))

In this formula, if the sales target in cell B2 is greater than 500,000, the formula returns “Excellent.”

If it’s between 400,000 and 500,000, the formula returns “Good.”

If it’s between 275,000 and 400,000, the formula returns “Average.” And if it’s below 275,000, the formula returns “Poor.”

Example #4: Use Multiple IF Statements in Excel to Check For Errors and Return Error Messages

Suppose we have the following dataset of students’ English test scores. Some scores are less than 0 or greater than 100, and there are no scores in some cases.

We want to use nested IF statements to check for scores in column B and display error messages in column C if there are no scores or the scores are less than 0 or greater than 100.

If the score in column B is valid, we want the formula to return an empty string in column C.

Here are the steps to follow:

• Drag the Fill Handle to copy the formula down the column.

The error messages are shown in column C.

Explanation of the formula 

=IF(OR(B2<0,B2>100),”Score out of range”,IF(ISBLANK(B2),”Invalid score”,””))

This formula uses the OR function to check if the score in cell B2 is less than 0 or greater than 100, and if it is, it returns the error message “Score out of range.”

The formula also uses the ISBLANK function to check if cell B2 is blank, and if it is, it returns the error message “Invalid score.”

If there is no error, the formula returns an empty string, meaning no message is displayed in column B.

Alternatives to Using Multiple IF Statements in Excel

Formulas using nested IF statements can become difficult to read and manage if we have more than a few conditions to test.

In addition, if we exceed the maximum allowed limit of 64 nested IF statements, we will get an error message.

Fortunately, Excel offers alternative ways to use instead of nested IF functions, especially when we need to test more than a few conditions. 

We present the alternative ways in this tutorial.

Alternative #1: Use the IFS Function

The IFS function tests whether one or more conditions are met and returns a value corresponding to the first TRUE condition.

Before the release of the IFS function in 2018 as part of the Excel 365 update, the only way to test multiple conditions and return a corresponding value in Excel was to use nested IF statements.

However, multiple IF statements have the downside of resulting in unwieldy formulas that are difficult to read and maintain.

In some situations, the IFS function is designed to replace the need for multiple IF functions.

The syntax of the IFS function is more straightforward and easier to read than nested IF statements, and it can handle up to 127 conditions.

Here’s an example:

We want to use the IFS function to assign letter grades to the students based on their scores. 

The student’s letter grades are shown in column C.

=IFS(B2>=90, “A”, B2>=80, “B”, B2>=70, “C”, B2>=60, “D”, B2<60, “F”)

This formula tests the score in cell B2 against each condition and returns the corresponding grade letter when the condition is true. 

Limitation of IFS Function

The IFS function in Excel is designed to simplify complex nested IF statements.

However, there are situations where the IFS function may not be able to replace nested IF functions completely.

One such situation is when you must calculate or operate based on a condition or set of conditions.

While the IFS function can return a value or text string based on a condition, it cannot perform calculations or operations on that value like nested IF statements.

Another situation where the IFS function may be less useful is when you need to test for a range of conditions rather than just a specific set.

This is because the IFS function requires you to specify each condition and corresponding result separately, which can become cumbersome if you have many conditions to test—in contrast, nested IF statements allow you to test for a range of conditions using logical operators like AND and OR.

The IFS function is a powerful tool for simplifying complex logical tests in Excel.

However, there may be situations where nested IF statements are more appropriate for your needs.

We recommend that you consider both options and choose the one that best fits the specific requirements of your task.

Alternative #2: Use Nested IF Functions

We can use multiple IFS functions in a formula if we have more than one condition to test.

For example, let’s say we have the following dataset of student names and scores on a Physics test in columns A and B.

We want to assign a letter grade to each score and include a pass or fail designation based on whether the score is above or below 75.

Here are the steps to use:

• Select cell C2 and enter the following formula

The letter grade and designation of the student scores are displayed in column C.

=IFS(B2>=90,”A”,B2>=80,”B”,B2>=70,”C”,B2>=60,”D”,B2<60,”F”)&”  “&IFS(B2>=75,”Pass”,B2<75,”Fail”)

This formula uses the first IFS function to assign a letter grade based on the score in column A and the second IFS function to give a pass/fail designation based on the score in column A.

The two IFS functions are combined using the ampersand (&) operator to create a single text string that displays each score’s letter grade and pass/fail designation.

Alternative #3: Use the Combination of CHOOSE and XMATCH Functions

The CHOOSE function selects a value or action from a value list based on an index number.

The XMATCH function locates and returns the relative position of an item in an array. We can combine these functions in a formula instead of nested IF functions.

Here’s an example:

Suppose we have the following dataset showing some students’ scores and letter grades on a Biology test. 

We want to use a formula combining the CHOOSE and XMATCH functions to assign corresponding grade points in column D to each letter grade.

• Select cell D2 and type in the below formula:

The grade points for each student are displayed in column D.

=CHOOSE(XMATCH(C2,{“F”,”E”,”D”,”C”,”B”,”A”},0),0,1,2,3,4,5)

This formula applies the XMATCH function to find the position of the letter grade in the array {“F”,”E”,”D”,”C”,”B”,”A”}, and then uses the CHOOSE function to return the corresponding grade points.

Alternative #4: Use the VLOOKUP Function

The VLOOKUP function looks for a value in the leftmost column of a table and then returns a value in the same row from a specified column.

We can use the VLOOKUP function instead of nested IF functions in Excel.

The following is an example of using the VLOOKUP function instead of nested IF functions in Excel:

We want to use the VLOOKUP function to assign grade points to each student’s letter grade in column D.

We use the steps below:

• Create a table that lists the grades and their corresponding grade points in cell range F1:G7.

• In cell D2, type the following formula:

Note: Use the dollar signs to lock down the cell range F2:G7.

The grade points for each student appear in column D.

=VLOOKUP(C2,$F$2:$G$7,2,FALSE)

This formula uses the VLOOKUP function to look up the grade in cell C2 in the table in F2:G7 and return the corresponding grade point in the second column (i.e., column G).

The “FALSE” argument ensures that an exact match is required.

Alternative #5: Use a User-Defined Function

If you need to test more than a few conditions, consider creating a User Defined Function in VBA that can handle many conditions. 

Here’s an example of using VBA code to replace nested IF functions in Excel:

Suppose we have the following dataset showing the sales of specific salespeople in a particular month.

We want to use a User Defined Function to calculate the commission for each salesperson based on the following rates:

• If the total sales are less than $10,000, the commission rate is 8%.
• If the total sales are equal to or greater than $10,000 but less than $20,000, the commission rate is 10.5%.
• If the total sales are equal to or greater than $20,000 but less than $40,000, the commission rate is 12%.
• If the sales are equal to or greater than $40,000, the commission rate is 14%
• Open the worksheet containing the sales dataset.
• Press Alt + F11 to launch the Visual Basic Editor .
• Click Insert on the menu bar and choose Module to insert a new module.

• Enter the following VBA code.
• Save the function procedure and the workbook as a Macro-Enabled Workbook .
• Press Alt + F11 to switch to the active worksheet with the sales dataset.

The commission for each salesperson is displayed in column C.

This VBA function takes the sales amount as an argument and returns the corresponding commission.

The User-Defined Function is a much simpler and easier-to-read solution than using nested IF functions.

This tutorial showed four examples of using nested IF statements in Excel and gave five alternatives to using multiple IF statements in Excel. We hope you found the tutorial helpful.

Other Excel tutorials you may find useful:

• Excel Logical Test Using Multiple If Statements in Excel [AND/OR]
• How to Compare Two Columns in Excel (using VLOOKUP & IF)
• Using IF Function with Dates in Excel (Easy Examples)
• COUNTIF Greater Than Zero in Excel
• BETWEEN Formula in Excel (Using IF Function) – Examples
• Count Cells Less than a Value in Excel (COUNTIF Less)

Steve Scott

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

2 thoughts on “Multiple If Statements in Excel (Nested IFs, AND/OR) with Examples”

Hi Scott, Extremely useful information here. Saves a lot of time to whomever needs, like I did. Thank you!! /Richard

hello, I have a situation where I need a formula for the bellow: If column A2 is not null than copy value from column A2, if null than brig value from B2 , if B2 is null also than populate sysdate. How can this be acheeved?

Leave a Comment Cancel reply

Save my name, email, and website in this browser for the next time I comment.

IF function – nested formulas and avoiding pitfalls

The IF function allows you to make a logical comparison between a value and what you expect by testing for a condition and returning a result if True or False.

=IF(Something is True, then do something, otherwise do something else)

So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

IF statements are incredibly robust, and form the basis of many spreadsheet models, but they are also the root cause of many spreadsheet issues. Ideally, an IF statement should apply to minimal conditions, such as Male/Female, Yes/No/Maybe, to name a few, but sometimes you might need to evaluate more complex scenarios that require nesting* more than 3 IF functions together.

* “Nesting” refers to the practice of joining multiple functions together in one formula.

Technical details

Use the IF function, one of the logical functions , to return one value if a condition is true and another value if it's false.

IF(logical_test, value_if_true, [value_if_false])

For example:

=IF(A2>B2,"Over Budget","OK")

=IF(A2=B2,B4-A4,"")

While Excel will allow you to nest up to 64 different IF functions, it’s not at all advisable to do so. Why?

Multiple IF statements require a great deal of thought to build correctly and make sure that their logic can calculate correctly through each condition all the way to the end. If you don’t nest your formula 100% accurately, then it might work 75% of the time, but return unexpected results 25% of the time. Unfortunately, the odds of you catching the 25% are slim.

Multiple IF statements can become incredibly difficult to maintain, especially when you come back some time later and try to figure out what you, or worse someone else, was trying to do.

If you find yourself with an IF statement that just seems to keep growing with no end in sight, it’s time to put down the mouse and rethink your strategy.

Let’s look at how to properly create a complex nested IF statement using multiple IFs, and when to recognize that it’s time to use another tool in your Excel arsenal.

Following is an example of a relatively standard nested IF statement to convert student test scores to their letter grade equivalent.

=IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

This complex nested IF statement follows a straightforward logic:

If the Test Score (in cell D2) is greater than 89, then the student gets an A

If the Test Score is greater than 79, then the student gets a B

If the Test Score is greater than 69, then the student gets a C

If the Test Score is greater than 59, then the student gets a D

Otherwise the student gets an F

This particular example is relatively safe because it’s not likely that the correlation between test scores and letter grades will change, so it won’t require much maintenance. But here’s a thought – what if you need to segment the grades between A+, A and A- (and so on)? Now your four condition IF statement needs to be rewritten to have 12 conditions! Here’s what your formula would look like now:

=IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-", IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))

It’s still functionally accurate and will work as expected, but it takes a long time to write and longer to test to make sure it does what you want. Another glaring issue is that you’ve had to enter the scores and equivalent letter grades by hand. What are the odds that you’ll accidentally have a typo? Now imagine trying to do this 64 times with more complex conditions! Sure, it’s possible, but do you really want to subject yourself to this kind of effort and probable errors that will be really hard to spot?

Tip:  Every function in Excel requires an opening and closing parenthesis (). Excel will try to help you figure out what goes where by coloring different parts of your formula when you’re editing it. For instance, if you were to edit the above formula, as you move the cursor past each of the ending parentheses “)”, its corresponding opening parenthesis will turn the same color. This can be especially useful in complex nested formulas when you’re trying to figure out if you have enough matching parentheses.

Additional examples

Following is a very common example of calculating Sales Commission based on levels of Revenue achievement.

=IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))

This formula says IF(C9 is Greater Than 15,000 then return 20%, IF(C9 is Greater Than 12,500 then return 17.5%, and so on...

While it’s remarkably similar to the earlier Grades example, this formula is a great example of how difficult it can be to maintain large IF statements – what would you need to do if your organization decided to add new compensation levels and possibly even change the existing dollar or percentage values? You’d have a lot of work on your hands!

Tip:  You can insert line breaks in the formula bar to make long formulas easier to read. Just press ALT+ENTER before the text you want to wrap to a new line.

Here is an example of the commission scenario with the logic out of order:

Can you see what’s wrong? Compare the order of the Revenue comparisons to the previous example. Which way is this one going? That’s right, it’s going from bottom up ($5,000 to $15,000), not the other way around. But why should that be such a big deal? It’s a big deal because the formula can’t pass the first evaluation for any value over $5,000. Let’s say you’ve got $12,500 in revenue – the IF statement will return 10% because it is greater than $5,000, and it will stop there. This can be incredibly problematic because in a lot of situations these types of errors go unnoticed until they’ve had a negative impact. So knowing that there are some serious pitfalls with complex nested IF statements, what can you do? In most cases, you can use the VLOOKUP function instead of building a complex formula with the IF function. Using VLOOKUP , you first need to create a reference table:

=VLOOKUP(C2,C5:D17,2,TRUE)

This formula says to look for the value in C2 in the range C5:C17. If the value is found, then return the corresponding value from the same row in column D.

=VLOOKUP(B9,B2:C6,2,TRUE)

Similarly, this formula looks for the value in cell B9 in the range B2:B22. If the value is found, then return the corresponding value from the same row in column C.

Note:  Both of these VLOOKUPs use the TRUE argument at the end of the formulas, meaning we want them to look for an approxiate match. In other words, it will match the exact values in the lookup table, as well as any values that fall between them. In this case the lookup tables need to be sorted in Ascending order, from smallest to largest.

VLOOKUP is covered in much more detail here , but this is sure a lot simpler than a 12-level, complex nested IF statement! There are other less obvious benefits as well:

VLOOKUP reference tables are right out in the open and easy to see.

Table values can be easily updated and you never have to touch the formula if your conditions change.

If you don’t want people to see or interfere with your reference table, just put it on another worksheet.

Did you know?

There is now an IFS function that can replace multiple, nested IF statements with a single function. So instead of our initial grades example, which has 4 nested IF functions:

It can be made much simpler with a single IFS function:

=IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")

The IFS function is great because you don’t need to worry about all of those IF statements and parentheses.

Note:  This feature is only available if you have a Microsoft 365 subscription . If you are a Microsoft 365subscriber, make sure you have the latest version of Office . Buy or try Microsoft 365

Need more help?

You can always ask an expert in the Excel Tech Community  or get support in  Communities .

Related Topics

Video: Advanced IF functions IFS function (Microsoft 365, Excel 2016 and later) The COUNTIF function will count values based on a single criteria The COUNTIFS function will count values based on multiple criteria The SUMIF function will sum values based on a single criteria The SUMIFS function will sum values based on multiple criteria AND function OR function VLOOKUP function Overview of formulas in Excel How to avoid broken formulas Detect errors in formulas Logical functions Excel functions (alphabetical) Excel functions (by category)

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

How to use the IF and AND function in Excel (Examples)

The IF function and the AND function belong to the logical functions’ library of Microsoft Excel.

Both these functions are superheroes in their domain – but when put together, the IF AND function makes an unstoppable superhero duo.

In the tutorial below, I will teach you how this function works. With two conditions, multiple conditions, and also when nested together.

So grab your sample practice workbook for this tutorial here and tag along with me 🏍

Table of Contents

If and function in excel, if and function with multiple conditions, nested if and function, must-knows about using the if and function of excel.

The IF AND function in Microsoft Excel is the combination of two top-notch logical functions of Excel. The IF function and the AND function.

The IF function works to run a given condition and return a certain value if the condition meets or another value if the condition fails.

Like you test a range of values to see if they are greater than 400 using the simple IF function.

The AND function on the other hand is a simple logical function that can test multiple conditions co-exist and return a Boolean value (true or false) ✔

The AND function checks each value against both conditions (greater than 200 but lesser than 600) and returns TRUE only if both conditions are true.

So why do we even need to bring both these functions together?

That’s because the IF function can only test a single condition by itself. It doesn’t allow testing multiple conditions to return a specified value.

Similarly, the AND function can test multiple conditions in one go but is only capable of returning Boolean values – not the values you specify.

The IF AND function of Excel is meant to test multiple conditions and accordingly return the value_if_true or value_if_false.

How do you use the IF AND function in Excel

The IF AND function makes a deadly combo in Excel and I am going to illustrate that here through an example.

Here I have a list of students and their grades in academics, as well as in extra-curricular activities 📋

Per the rules, only those students pass who:

• Scored an A+ in academics
• And A+ in extra-curriculars

Low-key, I am just so grateful to have completed my school 🙏

To have the pass/fail results populated in Column D, let’s write the IF AND function as follows:

Step 1) Begin by writing the AND function.

The syntax of this formula says that:

• First Condition: Cell B2 should be equal to the text value “A+” (note the quotation marks I have used to specify a text value to Excel)
• Second Condition: Cell value for C2 should be equal to the text value “A+”

AND function checks for the first condition and the second condition, both to be true. Only then if returns TRUE. If either of the conditions fails, it returns FALSE.

I dragged and dropped it to the whole list to learn only Benjamin and Elijah yielded TRUE.

Step 2) Nest the above function into the IF formula as below:

The first argument of the IF function is the logical test. We have set the AND function in its place. The IF function will now run the AND function as the logical test.

Step 3) Specify the value_if_true (if both the conditions are true) as “Pass”.

Step 4) Specify the value_if_false (if any of the conditions or both the conditions are false) as “Fail”.

Step 5) All set – hit enter for results.

Step 6) Drag and drop the above formula to the entire result.

The IF AND function evaluates both the given conditions and returns the pass/fail result for each student.

Although the results of students are not very encouraging (only 2 passed), the results of the IF AND function are amazing!

The above section was about running kind of a basic IF AND function. As we move forward, why run only two conditions? Why not more?

To your surprise, the IF AND function is very well capable of running 3, 4, 5 and so on up to 255 conditions.

Excel 2007 and higher versions can support up to 255 arguments in any function. The AND function can hence run up to 255 logical tests.

Want to see it happen? Let’s go.

With the same example as above, passing now gets even more difficult 🤔

For any student to pass now, he must:

• Score A+ in academics
• Score A+ in extra-curricular
• Have 90% or higher Attendance; and
• Have his behavior graded as Excellent

Time to see if anyone passes the above criteria.

Step 1) Write the AND function like below.

Only the text values need to be enclosed in quotation marks. Numbers (like 90%) need not be enclosed in quotation marks or else Excel will treat them as text values too.

This time the AND function translates to:

• First Condition: Cell B2 should be equal to the text value “A+”
• Second Condition: Cell C2 should be equal to the text value “A+”
• Third Condition: Cell D2 should be equal to or greater than 90%
• Fourth Condition: Cell E2 should be equal to the text value “A+”

Step 2) Hit enter to see what we get.

Step 3) Nest the above function into the IF function as below:

So, we have our logical test ready.

Step 4) Add the next two arguments for value_if_true and value_if_false as “Pass” and “Fail”, respectively.

Step 5) Fingers crossed. It’s time we see the results in our worksheet.

One more down – with the criteria so strict and four simultaneously running conditions, only Benjamin makes it to the students who passed. Better luck next time to the rest of them!

But yes, you must not stop here – make your own set of formulas with dozens of conditions and see how powerful the IF AND statement gets.

The IF AND combination can be tweaked in another way, too – and the results will bowl you over.

This is the situation where you want Excel to run a logical test and return results based on how the logical test performed.

However, the results are not just limited to two values (the value_if_true and value_if_false), but more.

The following example will explain to you how the nested IF AND function works.

So, here’s a restaurant with multiple branches that are assessed based on two of their KPIs i.e., Food and Ambience 🍽

The KPI sheet is simple:

• Food and Ambience both graded above 4 > Excellent
• Food and Ambience both graded above 3 > Good
• Food and Ambience both graded equal to or above 2 > Need Improvement
• Food and Ambience are both graded below 2 > Shut Down

But the point is, how do we translate the aforementioned KPIs into an Excel formula?

Here’s how:

Step 1) Begin writing from the first KPI using the IF AND function:

We’re just telling Excel to return the performance score as Excellent if both the Food and Ambience grades exceed 4.

But – in case both the conditions don’t turn true (any of the grades is equal to or less than 4), in place of the value_if_false, we will nest another IF AND function.

Step 2) As the value_if_false for this function write a new IF AND function.

With this, we are telling Excel that if the first condition isn’t met, run another IF AND function.

The nested IF AND function checks if both the Food and Ambience grades exceed 3. If they do, it returns the value_if_true i.e., Good.

Step 3) Repeating the same science as above, we will nest another IF AND function in the following formula (in place of the Value_if_false) :

Once again, the formula above says that:

If none of the branches exceed grade 4 or 3 for both KPIs, check if any branch achieves a score of 2 or above for both KPIs.

If any branch does, return the performance score “Need Improvement”.

Step 4) Next, specify the final value_if_false as “Shut Down”.

With that, Excel knows that if none of the branches exceeds grade 4 or 3 or at least meets grade 2 for both the KPIs, it needs to be “Shut Down”.

Step 5) Hit Enter to have your results populated.

Main Boulevard–I stays on top with other branches next in line 🥇

And we’re probably bidding goodbye to the branch in Orchard Street since it runs Grade 2 in food and Grade 1 in Ambience – rightly so.

That’s how the nested IF AND function works.

I get it looks clumsy (as the formula keeps getting longer and longer) but the results it produces are phenomenal.

By now, I assume you’re a pro at playing around with IF AND statements. Keep up with that but here are some pointers to keep in mind about the IF AND function:

• The IF AND function is not case-sensitive. So, it will not differentiate between texts that are only different in terms of case sensitivity.
• There’s a certain limit to the arguments you can give to the IF AND function. A maximum of 255 arguments and total formula length of 8192 characters is all it offers.
• To nest the IF AND function appropriately, follow a logical sequence to structure your conditions.
• As the formula continues to grow, make sure you use the right number of parentheses in it.

The IF function is one of the most powerful and useful functions of Excel 💪

To make the most out of the IF function, make sure you know all the ins and outs of how this function works. My step-by-step blog on how to use the IF function will help you with that.

Additionally, there are so many more functions that you will see clubbed with the IF Function in Excel like the SUMIF function , MEDIANIF function , COUNTIF function , AVERAGEIF function , and others.

Check out our articles that cover all the smart IF-related functions to become a pro at using them.

Written by Kasper Langmann

Hi, I'm  Kasper Langmann 👋

I'm the co-founder of Spreadsheeto, a certified Microsoft Office Specialist, and a Microsoft MVP.

With over 10 years of experience, I’ve taught Excel to millions of people worldwide.

I spent over 20 hours researching and writing this tutorial.

Last updated on August 29th, 2024.

Before you go, sign up for my free Excel course (+100,000 students) ->

• Ablebits blog
• If Statement

Excel Nested IF statement: examples, best practices and alternatives

The tutorial explains how to use the nested IF function in Excel to check multiple conditions. You will also learn a few other functions that could be good alternatives to using a nested formula in Excel.

How do you usually implement a decision-making logic in your Excel worksheets? In most cases, you'd use an IF formula to test your condition and return one value if the condition is met, another value if the condition is not met. To evaluate more than one condition and return different values depending on the results, you nest multiple IFs inside each other.

Though very popular, the nested IF statement is not the only way to check multiple conditions in Excel. In this tutorial, you will find a handful of alternatives that are definitely worth exploring.

Excel nested IF statement

Here's the classic Excel nested IF formula in a generic form:

You can see that each subsequent IF function is embedded into the value_if_false argument of the previous function. Each IF function is enclosed in its own set of parentheses, but all the closing parentheses are at the end of the formula.

Our generic nested IF formula evaluates 3 conditions, and returns 4 different results (result 4 is returned if none of the conditions is TRUE). Translated into a human language, this nested IF statement tells Excel to do the following:

As an example, let's find out commissions for a number of sellers based on the amount of sales they've made:

In math, changing the order of addends does not change the sum. In Excel, changing the order of IF functions changes the result. Why? Because a nested IF formula returns a value corresponding to the first TRUE condition . Therefore, in your nested IF statements, it's very important to arrange the conditions in the right direction - high to low or low to high, depending on your formula's logic. In our case, we check the "highest" condition first, then the "second highest", and so on:

If we placed the conditions in the reverse order, from the bottom up, the results would be all wrong because our formula would stop after the first logical test (B2>=1) for any value greater than 1. Let's say, we have $100 in sales - it is greater than 1, so the formula would not check other conditions and return 3% as the result.

If you'd rather arrange the conditions from low to high, then use the "less than" operator and evaluate the "lowest" condition first, then the "second lowest", and so on:

=IF($B2<1, 0%, IF($B2<51, 3%, IF($B2<101, 5%, IF($B2<=150, 7%, 10%))))

As you see, it takes quite a lot of thought to build the logic of a nested IF statement correctly all the way to the end. And although Microsoft Excel allows nesting up to 64 IF functions in one formula, it is not something you'd really want to do in your worksheets. So, if you (or someone else) are gazing at your Excel nested IF formula trying to figure out what it actually does, it's time to reconsider your strategy and probably choose another tool in your arsenal.

Nested IF with OR/AND conditions

In case you need to evaluate a few sets of different conditions, you can express those conditions using OR as well as AND function, nest the functions inside IF statements, and then nest the IF statements into each other.

Nested IF in Excel with OR statements

By using the OR function you can check two or more different conditions in the logical test of each IF function and return TRUE if any (at least one) of the OR arguments evaluates to TRUE. To see how it actually works, please consider the following example.

Supposing, you have two columns of sales, say January sales in column B and February sales in column C. You wish to check the numbers in both columns and calculate the commission based on a higher number. In other words, you build a formula with the following logic: if either Jan or Feb sales are greater than $150, the seller gets 10% commission, if Jan or Feb sales are greater than or equal to $101, the seller gets 7% commission, and so on.

To have it done, write a few OF statements like OR(B2>150, C2>150) and nest them into the logical tests of the IF functions discussed above. As the result, you get this formula:

=IF(OR(B2>150, C2>150), 10%, IF(OR(B2>=101, C2>=101),7%, IF(OR(B2>=51, C2>=51), 5%, IF(OR(B2>=1, C2>=1), 3%, ""))))

Nested IF in Excel with AND statements

If your logical tests include multiple conditions, and all of those conditions should evaluate to TRUE, express them by using the AND function .

For example, to assign the commissions based on a lower number of sales, take the above formula and replace OR with AND statements. To put it differently, you tell Excel to return 10% only if Jan and Feb sales are greater than $150, 7% if Jan and Feb sales are greater than or equal to $101, and so on.

=IF(AND(B2>150, C2>150), 10%, IF(AND(B2>=101, C2>=101), 7%, IF(AND(B2>=51, C2>=51), 5%, IF(AND(B2>=1, C2>=1), 3%, ""))))

If you'd like to return 0% instead of blank cells, replace an empty string (''") in the last argument with 0%:

VLOOKUP instead of nested IF in Excel

When you are dealing with "scales", i.e. continuous ranges of numerical values that together cover the entire range, in most cases you can use the VLOOKUP function instead of nested IFs.

For starters, make a reference table like shown in the screenshot below. And then, build a Vlookup formula with approximate match , i.e. with the range_lookup argument set to TRUE.

Assuming the lookup value is in B2 and the reference table is F2:G5, the formula goes as follows:

=VLOOKUP(B2,$F$2:$G$5,2,TRUE)

By setting the last argument of your Vlookup formula to TRUE, you tell Excel to search for the closest match - if an exact match is not found, return the next largest value that is smaller than the lookup value. As the result, your formula will match not only the exact values in the lookup table, but also any values that fall in between.

For example, the lookup value in B3 is $95. This number does not exist in the lookup table, and Vlookup with exact match would return an #N/A error in this case. But Vlookup with approximate match continues searching until it finds the nearest value that is less than the lookup value (which is $50 in our example) and returns a value from the second column in the same row (which is 5%).

But what if the lookup value is less than the smallest number in the lookup table or the lookup cell is empty? In this case, a Vlookup formula will return the #N/A error. If it's not what you actually want, nest VLOOKUP inside IFERROR and supply the value to output when the lookup value is not found. For example:

=IFERROR(VLOOKUP(B2, $F$2:$G$5, 2, TRUE), "Outside range")

Important note! For a Vlookup formula with approximate match to work correctly, the first column in the lookup table must be sorted in ascending order , from smallest to largest.

IFS statement as alternative to nested IF function

In Excel 2016 and later versions, Microsoft introduced a special function to evaluate multiple conditions - the IFS function.

An IFS formula can handle up to 127 logical_test / value_if_true pairs, and the first logical test that evaluates to TRUE "wins":

In accordance with the above syntax, our nested IF formula can be reconstructed in this way:

=IFS(B2>150, 10%, B2>=101, 7%, B2>=51, 5%, B2>0, 3%)

Please pay attention that the IFS function returns the #N/A error if none of the specified conditions is met. To avoid this, you can add one more logical_test / value_if_true to the end of your formula that will return 0 or empty string ("") or whatever value you want if none of the previous logical tests is TRUE:

=IFS(B2>150, 10%, B2>=101, 7%, B2>=51, 5%, B2>0, 3%, TRUE, "")

Note. Like nested IF, Excel's IFS function returns a value corresponding to the first condition that evaluates to TRUE, which is why the order of logical tests in an IFS formula matters.

CHOOSE instead of nested IF formula in Excel

Another way to test multiple conditions within a single formula in Excel is using the CHOOSE function, which is designed to return a value from the list based on a position of that value.

Applied to our sample dataset, the formula takes the following shape:

=CHOOSE((B2>=1) + (B2>=51) + (B2>=101) + (B2>150), 3%, 5%, 7%, 10%)

In the first argument ( index_num ), you evaluate all the conditions and add up the results. Given that TRUE equates to 1 and FALSE to 0, this way you calculate the position of the value to return.

Tip. If none of the logical tests is TRUE, index_num is equal to 0, and the formula returns the #VALUE! error. An easy fix is wrapping CHOOSE in the IFERROR function like this:

=IFERROR(CHOOSE((B2>=1) + (B2>=51) + (B2>=101) + (B2>150), 3%, 5%, 7%, 10%), "")

For more information, please see Excel CHOOSE function with formula examples .

SWITCH function as a concise form of nested IF in Excel

In situations when you are dealing with a fixed set of predefined values, not scales, the SWITCH function can be a compact alternative to complex nested IF statements:

The SWITCH function evaluates expression against a list of values and returns the result corresponding to the first found match.

In case, you'd like to calculate the commission based on the following grades, rather than sales amounts, you could use this compact version of nested IF formula in Excel:

=SWITCH(C2, "A", 10%, "B", 7%, "C", 5%, "D", 3%, "")

Or, you can make a reference table like shown in the screenshot below and use cell references instead of hardcoded values:

=SWITCH(C2, $F$2, $G$2, $F$3, $G$3, $F$4, $G$4, $F$5, $G$5, "")

Note. The SWITCH function is only available in Excel 2016 and higher.

Concatenating multiple IF functions in Excel

As mentioned in the previous example, the SWITCH function was introduced only in Excel 2016. To handle similar tasks in older Excel versions, you can combine two or more IF statements by using the Concatenate operator (&) or the CONCATENATE function.

For example:

=(IF(C2="a", 10%, "") & IF(C2="b", 7%, "") & IF(C2="c", 5%, "") & IF(C2="d", 3%, ""))*1

As you may have noticed, we multiply the result by 1 in both formulas. It is done to convert a string returned by the Concatenate formula to a number. If your expected output is text, then the multiplication operation is not needed.

You can see that Microsoft Excel provides a handful of good alternatives to nested IF formulas, and hopefully this tutorial has given you some clues on how to leverage them in your worksheets. To have a closer look at the examples discussed in this tutorial, you are welcome to download our sample workbook below. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

You may also be interested in.

• If cell contains, then return value – multiple conditions
• VLOOKUP with IF statement in Excel
• Excel IF statement between two numbers or dates
• Check if two cells match or multiple cells are equal

Table of contents

228 comments

I have a spreadsheet that has a column for name, active date and inactive date. I need to somehow do a count of users that were active during a certain month. For example, I need to know how many users were active in say, June 2024. According to the data below, the first 4 should be counted (test 2 and 3 is still active). Test 5 wouldn't be counted because they left in May. I have no idea how to write a formula for that. Can anyone help? A) B) C) Name Active date Inactive Date Test 11/15/23 06/15/24 Test2 09/28/23 Test3 05/11/24 Test4 12/06/23 08/15/24 Test5 01/15/24 05/03/24

Hello Candy! If I understand your task correctly, this article may be helpful: Excel SUMPRODUCT function with multiple criteria . The formula might look like this:

=SUMPRODUCT((B1:B5<=F1)*((C1:C5>=G1)+(C1:C5="")))

F1 - 30 June. G1 - 1 June.

Fyi the IFS function apparently behaves differently than nested IF(IF( ))s. IFS does not stop when it finds a true condition but instead evaluates everything, which can cause slowdowns with large datasets or complicated conditions/formulas when true. Although seemingly archaic, nested IF statements may be the way to go for some (if readability is less of a priority).

I am tracking the mileage from 42 potential combinations of "to" and "from" locations. I have one column of the "from" location (CO, JWE, EL, PA, etc.) and a second column of the "to" location (CO, JWE, EL, PA, etc.). I want to calculate if column 1 says CO and column 2 says BDM then I want column 3 to generate the text "2.1". Here is my formula. What am I doing wrong, or is there a limit to the amount of ifs I can use in one cell?

=IF(AND(D2="CO", E2="PK"), "1.2", IF(AND(D2="CO", E2="JWE"), "1.2", IF(AND(D2="CO", E2="EL"), "2.3", IF(AND(D2="CO", E2="PA"), "2.0", IF(AND(D2="CO", E2="NV"), "1.9", IF(AND(D2="CO", E2="BDM"), "2.1", IF(AND(D2="PK", E2="CO"), "1.2", IF(AND(D2="PK", E2="JWE"), "0.2", IF(AND(D2="PK", E2="EL"), "1.6", IF(AND(D2="PK", E2="PA"), "2.1”, IF(AND(D2="PK", E2="NV"), "0.6", IF(AND(D2="PK", E2="BDM"), "1.4", IF(AND(D2="JWE", E2="CO"), "1.2", IF(AND(D2="JWE", E2="PK"), "0.2", IF(AND(D2="JWE", E2="EL"), "1.6", IF(AND(D2="JWE", E2="PA"), "1.8", IF(AND(D2="JWE", E2="NV"), "1.0", IF(AND(D2="JWE", E2="BDM"), "1.4", IF(AND(D2="EL", E2="CO"), "2.3", IF(AND(D2="EL", E2="PK"), "1.6", IF(AND(D2="EL", E2="JWE"), "1.5", IF(AND(D2="EL", E2="PA"), "1.4", IF(AND(D2="EL", E2="NV"), "0.9", IF(AND(D2="EL", E2="BDM"), "2.4", IF(AND(D2="PA", E2="CO"), "2.0", IF(AND(D2="PA", E2="PK"), "2.1", IF(AND(D2="PA", E2="JWE"), "1.8", IF(AND(D2="PA", E2="EL"), "1.4", IF(AND(D2="PA", E2="NV"), "1.9", IF(AND(D2="PA", E2="BDM"), "3.0", IF(AND(D2="NV", E2="CO"), "1.9", IF(AND(D2="NV", E2="PK"), "0.6", IF(AND(D2="NV", E2="JWE"), "1.0", IF(AND(D2="NV", E2="EL"), "0.9", IF(AND(D2="NV", E2="PA"), "1.9", IF(AND(D2="NV", E2="BDM"), "1.6", IF(AND(D2="BDM", E2="CO"), "2.1", IF(AND(D2="BDM", E2="PK"), "1.4", IF(AND(D2="BDM", E2="JWE"), "1.4", IF(AND(D2="BDM", E2="EL"), "2.4", IF(AND(D2="BDM", E2="PA"), "3.0", IF(AND(D2="BDM", E2="NV"), "1.6"))))))))))))))))))))))))))))))))))))))))))

Hi! You used the wrong quotation marks: "2.1”. For multiple conditions, I recommend using the IFS formula as described in this article: The new Excel IFS function instead of multiple IF .

Required an alternate formula for below one IF($A$119<=$A127,$A$119:$E$119,IF($A$118<=$A127,$A$118:$E$118,$A$117:$E$117)) need to reduce the number of time to select the row if the rows shall be more than 100

Sorry, it's not quite clear what you are trying to achieve. Please clarify your specific problem or provide additional information to understand what you need.

In vlookup function in order to choose the specific row in the table based on certain cell condition we are using multiple if condition If table contains multiple row what is the alternative method

Hi! From what you've described, it's hard to fully understand what you're being asked to do. Maybe this article will be helpful: How to Vlookup multiple values in Excel with criteria .

The following nested IF formula is skipping over the third and fourth lines if the second line returns an #N/A. I've tried alternative approaches (i.e., CHOOSE, etc.) which didn't work either. The basic idea is for the cell to display a certain text given the appearance of a cell reference within specific lists which are located on disparate Sheets. For future maintenance, I wish to avoid utilizing VLOOKUP. The data have all been formatted as Text.

=IFNA(IF('FAF Queues'!E2="","", IF('Queues'!E2=INDEX('State 1 Full'!A:A, MATCH('Queues'!E2, 'State 1 Full'!A:A, 0)), "State 1", IF('Queues'!E2=INDEX('State 1 Custom'!A:A, MATCH('Queues'!E2, 'State 1 Custom'!A:A, 0)), "Custom", IF('Queues'!E2=INDEX('State 2 Full'!A:A, MATCH('Queues'!E2, 'State 2 Full'!A:A, 0)), "State 2", "Non Urgent")))), "Not working")

Is there a way to ensure each nested IF is processed in succession, despite the #N/A being returned? #N/A occurs whether or not I use the IFNA piece of the formula.

Correction to formula to reduce confusion:

=IFNA(IF('Queues'!E2="","", IF('Queues'!E2=INDEX('State 1 Full'!A:A, MATCH('Queues'!E2, 'State 1 Full'!A:A, 0)), "State 1", IF('Queues'!E2=INDEX('State 1 Custom'!A:A, MATCH('Queues'!E2, 'State 1 Custom'!A:A, 0)), "Custom", IF('Queues'!E2=INDEX('State 2 Full'!A:A, MATCH('Queues'!E2, 'State 2 Full'!A:A, 0)), "State 2", "Non Urgent")))), "Not working")

**NOTE: "Not working" is simply being used to identify the instances of #N/A, and it will be changed if this ends up working.

Hi! To ignore the error message and replace it with a value or formula, use the IFERROR function. You can find the examples and detailed instructions here: How to use IFERROR in Excel with formula examples . I don't have your data, so I can't change your formula.

My question is really: why does this nested IF skip from IF('Queues'!E2=INDEX('State 1 Full'!A:A, MATCH('Queues'!E2, 'State 1 Full'!A:A, 0)), "State 1" to "Not working"? What am I missing?

Shouldn't it move from IF('Queues'!E2=INDEX('State 1 Full'!A:A, MATCH('Queues'!E2, 'State 1 Full'!A:A, 0)), "State 1"

to IF('Queues'!E2=INDEX('State 1 Custom'!A:A, MATCH('Queues'!E2, 'State 1 Custom'!A:A, 0)), "Custom"

then to IF('Queues'!E2=INDEX('State 2 Full'!A:A, MATCH('Queues'!E2, 'State 2 Full'!A:A, 0)), "State 2", "Non Urgent"

before going to "Not working"?

Hi! I already answered you: use the IFERROR or IFNA function. If INDEX('State 1 Full'!A:A, MATCH(Queues!E1, 'State 1 Full'!A:A, 0)) returns #N/A, then the formula immediately returns #N/A. Read carefully the article on the IFERROR function I recommended, as well as How to use IFNA function in Excel with examples .

I'm not sure how to do this problem, but I am trying to find the insurance plan rate for a person in a table (exported insurance bill) that has columns for name, insurance plan number (4 levels - 1 is only for executives so not really used), and coverage level. I ADD a column for whether or not the employee is in category A, O or T (but now A&O have the same rates, so would only need if T or not T classification) [ when I have time, I will update our master conversion of employees, but that has yet to arrive].

I need to look up the plan coverage rate for an employee based on their classification (if equals T), then if T find the plan name and coverage level and return the rate or if NOT T, do the same, but for it's own set of rates.

I set up master arrays with coverage level in column A1 (COL HEADING) and variables in A2:A5 (IND, ESC, ECH & FAM) Plan numbers in column headings for col B-D (A02, A03, A04) Rates are in grid of B2:D5

all others (not T classified) CVG_LVL Plan 1 (A02) Plan 2 (A03) Plan 3 (A04) Employee 90.00 $100.00 $138.00 Employee + Spouse 275.00 $300.00 $350.00 Employee + Child(ren) 250.00 $200.00 $320.00 Employee + Family 450.00 $500.00 $530.00

for T classified CVG_LVL Plan 1 (A02) Plan 2 (A03) Plan 3 (A04) Employee 65.00 $71.00 $100.00 Employee + Spouse 230.00 $261.00 $299.00 Employee + Child(ren) 205.00 $233.00 $263.00 Employee + Family 370.00 $423.00 $469.00

I'm stuck. I can get the xlookup to tell me the employee name and return their respective departments and percentages of the rate to code to each department, BUT I can't seem to figure out how to get the rate based on their classification and then given that, use the coverage level to ultimately return the rate for that employee.

Any suggestions is appreciated.

Hello! Unfortunately, I couldn't make sense of the large and complicated description of your data. Try to make it simpler, write source data and desired result.

Use a function or formula to calculate the commission for each truck driver based on this information: (Drivers' commission is a percentage of their Grand Total. Those with 70,000 or less, get a 5% commission. Drivers whose grand total is above 70,000 get a 10% commission.) 3 marks

Study the article above carefully and get your 3 marks.

Hi, I am trying to get my formula to work. The end result would be if a1 > b1 then "apple". if b1> c1 then "orange" and if c1> a1 then " pineapple" however., I seem to get stuck when in the second row. my current formula is =IF(A>B1,"APPLE",IF(B1>A1,"ORANGE",IF(C1>B1,"PINEAPPLE",IF(A1>C1,"APPLE",IF(B1>C1,"ORANGE",IF(C1>A1,"Packout"))))))

Hi! In your case, several conditions can be executed simultaneously. So pay attention to this paragraph: Nested IF with OR/AND conditions. It is impossible to correct your formula, because you have not described these conditions.

Hello and thank you for great content! I'm having problems with my formula. In one cell I have an answer (C33="Yes"). In another cell (B32) I have a number value. The returned value differs based on the number value in cell B32. The formula pulls in the correct value for the very first condition where B32<65%. Every statement after the first one doesn't seem to work. When I enter a value of 65% or greater in B32 it just returns 0. Should I be using a different formula? Thank you for any help you can provide.

=IF(AND(C33="Yes",B32<65%),Values!V4, IF(AND(C33="Yes",65%<B32<75%),Values!V5, IF(AND(C33="Yes",75%<B32<80%),Values!V6, IF(AND(C33="Yes",80%<B32<85%),Values!V7, IF(AND(C33="Yes",85%<B32<90%),Values!V8, IF(AND(C33="Yes",90%<B32<95%),Values!V9,0))))))

Hi! The statement 65% Excel AND function .

Hi Alexander, I didn't refresh my browser so I didn't see your reply until after posting the solution we found. Thank you for taking a look!

I figured it out! Well, actually, my husband did. For anyone reading, the issue was that the B32 range wasn't recognized (i.e. 65%<B32<75%). Using this same example, since the condition for the value up to 65% is already accounted for in the previous condition, only B32<75% needs to be written in the next condition. Still open to simplifications to this formula if any exist. Thanks!

I am trying to write a formula that references a date range based on age that will return a value from a list of values to calculate a certain premium amount.

For example if the person is aged 54.85 then I need to return a value of 0.43 (the table reference is G$28) which is is the age range of 50 and less than 55 - i have written the following formula but it is not working for me at all

=IF(AND(F5=25,F5=29,F5=35,F5=40,F5=45,F5=50,F5=55,F5=60,F5=65,F570,G$32))))

and I have tried it this way

=IF(IF(F4=25,F4=30,F4=35,F4=40,F4=45,F4=50,F4=55,F4=60,F4=65,F470,G$32))))

Hi! Try using the approximate match Vlookup formula to search in value intervals. You can find the examples and detailed instructions here: How to Vlookup for approximate match . I hope it’ll be helpful. If something is still unclear, please feel free to ask.

Thank you, very well written article

I need some help with a formula

I have the below formula that calculates the sum of money someone would get for allowances for Breakfast, Lunch and Dinner when travelling. G20, H20, I20 – are separate criteria’s if blank add it if not do not add (you would indicate in these columns if a meal is provided, B, L, D) The Sheet H, I, J columns are the count column D20 is a criteria (location) and L7 is a criteria (status)

=IF(G20="",SUM((SUMIFS('SHEET as at 26 May 23'!$H$7:$H$30,'SHEET as at 26 May 23'!$B$7:$B$30,$D20,'SHEET as at 26 May 23'!$D$7:$D$30,$L$7))))+IF(H20="",SUM((SUMIFS('SHEET as at 26 May 23'!$I$7:$I$30,'SHEET as at 26 May 23'!$B$7:$B$30,$D20,'SHEET as at 26 May 23'!$D$7:$D$30,$L$7))))+IF(I20="",SUM((SUMIFS('SHEET as at 26 May 23'!$J$7:$J$30,'SHEET as at 26 May 23'!$B$7:$B$30,$D20,'SHEET as at 26 May 23'!$D$7:$D$30,$L$7))))

What I now need to do is subtract an amount for meals dependent on what time someone departs home or arrives back home I have a column for departure and arrival times

For departure

If depart before 07:00 = above formula

If depart after 07:01 subtract the answer to: SUMIFS('SHEET as at 26 May 23'!$H$7:$H$30,'SHEET as at 26 May 23'!$B$7:$B$30,$D20,'SHEET as at 26 May 23'!$D$7:$D$30,$L$7)

If depart after 13:01 subtract answer to: SUMIFS('SHEET as at 26 May 23'!$H$7:$H$30,'SHEET as at 26 May 23'!$B$7:$B$30,$D20,'SHEET as at 26 May 23'!$D$7:$D$30,$L$7) + SUM((SUMIFS('SHEET as at 26 May 23'!$I$7:$I$30,'SHEET as at 26 May 23'!$B$7:$B$30,$D20,'SHEET as at 26 May 23'!$D$7:$D$30,$L$7)

If depart after 19:01 = answer is zero

For arrival back home

If return prior to 07:00 = answer is zero

If return after 07:01 but before 13:00 answer is SUMIFS('SHEET as at 26 May 23'!$H$7:$H$30,'SHEET as at 26 May 23'!$B$7:$B$30,$D20,'SHEET as at 26 May 23'!$D$7:$D$30,$L$7)

If return after 13:01 but before 19:00 answer is SUMIFS('SHEET as at 26 May 23'!$H$7:$H$30,'SHEET as at 26 May 23'!$B$7:$B$30,$D20,'SHEET as at 26 May 23'!$D$7:$D$30,$L$7) + SUM((SUMIFS('SHEET as at 26 May 23'!$I$7:$I$30,'SHEET as at 26 May 23'!$B$7:$B$30,$D20,'SHEET as at 26 May 23'!$D$7:$D$30,$L$7)

If arrive after 19:01 answer is original formula.

I have played around with IF formulas and time but cant work out how to add formulas instead of an actual number

=IF(E20="","0",IF(E20<TIME(7,0,0),"1",IF(E20<TIME(13,0,1),".75",IF(E20TIME(19,0,1),"0")))))

I have also played around with trying to break it up into separate columns and then adding these columns together which works for another allowance structure although I now have 20 columns in the background with various formulas, But this allowance structure is not working and I am hoping I can make it tidier

Can you help please???

Hi! It is very difficult to understand a formula that contains unique references to your workbook worksheets. I cannot guess what is not working in your formulas. I have no data to check how these formulas work. Ask me a concrete question about the formula, and I will try to help.

I would like to produce an equation where the range of sales range will produce a dollar amount based on the commission %. The equation in the article is just producing a %.

Example: 0 - $50k apply 4.75% 50.01K to $150k apply 3% Over $150k apply 1.50%

Based on this scale, if the there were 1,000,000 of sales, the answer would be $18,125, but I can't get the IF formula to work.

Please re-check the article above since it covers your task. See first paragraph.

Hi all, I am trying to nest a lot of if statements together, some are just "if then", others are "if then else". I am having difficulty with the syntax. I can see you can use concatenate to combine multiple "if then else" statements together, but what if I wanted to combine a mix of "if then", "if then", "if then else", "if then else" for example. I keep getting a "too many arguments" error The details aren't important, just the syntax. My example is : =If(M7="Commencement",1500,IF(M7="Recommencement",750,IF(AND(M7="BAC 1st Qtr",O4*.5>7000),7000,O4*.5,IF(AND(M7="BAC 1st Qtr",O4*.5>4000),4000,O4*.9)))) I appreciate any assistance

Hi! Pay attention to this part of the formula.

IF(AND(M7="BAC 1st Qtr",O4*.5>7000),7000,O4*.5, IF....

This IF has two FALSE arguments. If you have many IF arguments, I recommend using the IFS function .

I have a report that I do daily, we have daily figures which are to be reported as they change, then the MTD figures which are added up, now in the dashboard sheet, I need to automate such that, when the date changes (set =TODAY()), then the "daily update" columns must pick from that particular day..I don't want use the IF function as it becomes too long.

Any alternative formula?

Hi! If I understand correctly, you want the current date to be written for each new value. Perhaps this instruction will be helpful to you: How to insert today date & current time as unchangeable time stamp . If it is not what you need, explain the problem in more detail.

how can i create a formula for this commands in excel kindly help Determine the commission percentages based on sales: For sales up to 500k, the commission percentage will be 1.5% For sales between 500k and 1m, the commission percentage will be 2% For sales above 1.5m, the commission percentage will be 2.5%

Hi! Pay attention to the first paragraph of the article above. It covers your case completely.

Great article!!!

The company categorizes all its staff as follows; all those who earns 900000 and above are categorized as Management, those who earn 800000 and above but below 900000 are termed as experts, those earning 700000 and above but below 800000 are called technical staffs and the rest are categories as Support staffs. Using a good Excel function, attach a category to each staff using the scenarios listed above.

Can someone help me with this if formula example?

Please read the above article carefully.

I am struggling to get result based on these criteria table:

Age: Earning Point = 45 $11800 10 = 45 $21000 20

What do you want to calculate exactly? Your question is not entirely clear, please specify.

Age 45 Point $5600 $11800 10

$8800 $21000 20

Please help. Tried multiple ways - If(and) / If. Dont seem to work completely.

Post a comment

How to Use IF-THEN Statements with Text in Excel (7 Examples)

To demonstrate how to use IF-THEN statements with text in Excel, we will use the following dataset, which contains columns for  Product, Color, and Size :

Method 1 – Combining IF, SEARCH, and ISNUMBER Functions to Find a Certain Text String

Suppose you want to know how many products such as shirts or t-shirts are in our dataset. The text “ Shirt ” is common to both products, so we apply the formula based on the text “ Shirt ”.

• Enter the following formula in cell E5 :
• Press ENTER .

Formula Breakdown

• So, SEARCH(“shirt”, B5)—> returns 1 .
• Thus, ISNUMBER(1)—> returns TRUE .
• The IF function will return a logical test result. Here, “Found” —> when the logical test is TRUE it will return “ Found “. Basically, an Inverted Comma is a must for getting a text as the output. “Not Found”  —> denotes that when the logic fails it will return “ Not Found “.
• Drag the Fill Handle down to copy the formula to the other cells of the column.

The number of products that are shirts or t-shirts is returned.

Method 2 – Merging IF, and EXACT Functions to Match a Text String

Suppose you want to know how many products are Shirts .

• Enter the following formula in cell E5 .

• The EXACT function will check whether the cell value of B5 is as same as “Shirt” .
• The IF function will return a logical test result. If the EXACT function returns TRUE then the IF function will return “ Found ”. Otherwise, the IF function will return “ Not Found “.
• Drag the Fill Handle icon to copy the formula to the other cells of the column.

The number of products that are Shirts is returned.

Method 3  Using IF Function to Get Status of Availability Only

Suppose you want to know how many products are Shirts only.

Here, the IF function will return the output of a logical test.

• B5=”shirt” denotes the logical test, which will check whether the cell value of B5 is equal to shirt or not.
• “Found” —> when the cell value of B5 is equal to shirt then it will return Found . Here, an Inverted Comma is a must for getting a text as the output.
• “Not Found”  —> when the logic fails then it will return Not Found .

As a result, you will know how many products contain shirts .

Method 4 – Combining IF and ISTEXT Functions

Suppose you want to check whether the values in the Size column are correct. If the Size column contains a numerical value, then it will not be correct as the value should be alphabetic like XL, XXL, S, M, L, or XS. We will use the ISTEXT function to perform this task.

• Here, the ISTEXT function will check whether the cell value of B5 is text or not .
• The IF function will return “ Found ” when the ISTEXT function returns TRUE , else the IF function will return “ Not Found “.

Wrong Info will be returned as the status if the size of any product is not correct.

Method 5 – Combining OR Function with IF-THEN Statements to Find a Certain Text in Excel

Suppose you want to know if there is any products which is a shirt or has any shade of the color green . Steps: 

• The OR function will consider two logical tests. One is B5=”shirt” and other is ISNUMBER(SEARCH(“green”,C5)) .
• B5=”shirt”—> returns TRUE .
• So, SEARCH(“green”, C5)—> returns #VALUE!.
• Thus, ISNUMBER(#VALUE!)—> returns FALSE .
• OR(TRUE, FALSE)—> returns TRUE .
• The IF function will return Found when the logical test is TRUE otherwise it will return Not Found .

Method 6 – Merging AND Function with IF-THEN Statements to Search for a Text String

Suppose you want to know if is there any product which is both a shirt and any shade of the color green .

• The AND function will consider two logical tests. One is B5=”shirt” and other is ISNUMBER(SEARCH(“green”,C5)).
• B5= “shirt”—> returns TRUE .
• AND(TRUE, FALSE)—> turns FALSE .

As a result, you will find which products are shirts with any shade of green color.

Method 7 – Multiple IF-THEN Statements to Get Logical Output Depending on Various Conditions

Suppose you want to know if any shirt has sea green color and L size.

• Here, we have used a combination of three IF functions.
• Output: FALSE .
• So, this is a kind of chain logical test , where if only one test fails you will get FALSE as output.
• Drag the Fill Handle icon to copy the used formula respectively to the other cells of the column.

Returned will be the shirt having sea green color with L size.

Download Practice Workbook

<< Go Back to Text | If Cell Contains | Formula List | Learn Excel

What is ExcelDemy?

Tags: Excel If Cell Contains Text

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

• User Reviews
• List of Services
• Service Pricing

• Create Basic Excel Pivot Tables
• Excel Formulas and Functions
• Excel Charts and SmartArt Graphics
• Advanced Excel Training
• Data Analysis Excel for Beginners

Advanced Excel Exercises with Solutions PDF