EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Logical Functions in Excel Tutorial Excel IF AND Function
Secondary Sidebar
Excel Functions
  • Logical Functions in Excel
    • Excel IF AND Function
    • IFERROR Formula in Excel
    • SUMIF with Multiple Criteria
    • Excel SUMIFS with Dates
    • FALSE Function in Excel
    • Excel IF Function
    • Excel Logical Test
    • Excel XOR Function
    • Logical Functions Excel
    • IFERROR Excel Function
    • TRUE Function in Excel
    • OR Function in Excel
    • NOT in Excel
    • AND Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL ADVANCED COURSE
  • Excel Data Analysis Certification
  • Excel for Marketing Certification

Excel IF AND Function

By Manisha SharmaManisha Sharma

IF AND Function in Excel

Excel IF AND Function (Table of Contents)

  • IF AND Function in Excel
  • How to Use IF AND Function in Excel?

IF AND Function in Excel

This tutorial aims to show how to amalgamate IF and the AND functions in Excel to check several conditions in a single formula. Some of the items in nature are finite. Others seem to have endless potential, and interestingly the IF function seems like it has such properties.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,325 ratings)

Today, our objective is to see how and if we can combine IF and the AND function to evaluate multiple conditions simultaneously. So first, we will need to build the IF AND function and to do that, we will be required to join two separate functions – the IF and AND functions, in one single formula. So let us see how this can be achieved:

IF(AND(Logical1, Logical2,…), val_if_true, val_if_false)

IF AND Function syntax

The above formula has the following meaning: If the first condition, i.e. condition 1, is true and the second condition, i.e. condition 2, is also true, then do something, or else do something different.

How to Use IF AND Function in Excel?

Let’s understand how to use IF AND Function in Excel using some examples.

You can download this IF AND Function Excel Template here – IF AND Function Excel Template

Excel IF AND Function – Example #1

For example, let us understand this; let us create a formula whose objective is to check if B2 is “delivered” and check that C2 isn’t empty. Now, depending on the outcome, it would do one of the following:

  • If both condition 1 and condition 2 evaluate to TRUE, the order must be marked as “Closed”.
  • If anyone of the conditions evaluates to FALSE or both evaluate to FALSE, then an empty string has to be returned (“).

=IF(AND(B2=”delivered”, C2<>””), “Closed”, “”)

The IF AND function are demonstrated in the screenshot below:

Example 1

Note: While using the IF AND formula to evaluate certain conditions, it is important to keep in mind that uppercase and lowercase characters are considered similar. So, if the objective is to use a case-sensitive version of the IF AND formula, we have to wrap the arguments of AND inside the EXACT function.

Excel IF AND Function – Example #2

So far, in our earlier example, we were attempting to test two conditions of different cells. But in our daily lives, we often encounter situations where we may be required to run multiple tests on the same cell. An example that typically demonstrates this is checking if a cell value falls in between two numbers. That is where the IF AND function come in handy!

Now let us suppose that we have the sales numbers for certain orders in column B, and our job is to flag the amounts that are less than $100 but greater than $50. So, to approach this problem, first, we will insert the following formula in cell C2 and then proceed to copy it down the columns:

=IF(AND(B2>50, B2<100), “X”, “”)

Example 2

Excel IF AND Function – Example #3

If it is required that we include the peripheral values (100 and 50), then we will need to make use of greater than or equal to (>=) operator and the less than or equal to an operator (<=):

=IF(AND(B2>=50, B2<=100), “X”, “”)

Example 3-1

Moving on, to process other kinds of peripheral values without having to change the formula, we will input the maximum and minimum numbers in two different cells and provide the reference to those cells in our formula. To make the formula function appropriately for all the rows, make sure to utilize absolute references for the peripheral cells ($F$1 and $F$2 in this case):

Now we will apply the formula in cell C2.

=IF(AND(B2>=$F$1, B2<=$F$2), “X”, “”)

Example 3-2

Excel IF AND Function – Example #4

Employing a similar formula as in the above example, it is possible to check if a date lies within a specific range.

For example, we will flag all the dates between 30-Sep-2018 and 10-Sep-2018, including the peripheral dates. So here we see that we are faced with a unique problem: the dates can’t be directly supplied to the logical tests. To make the dates Excel-understandable, we will need to enclose them inside the DATEVALUE function, as shown below:

=IF(AND(B2>=DATEVALUE(“10-Sep-2018”), B2<=DATEVALUE(“30-Sep-2018”)), “X”, “”)

Example 4-1

It is also possible to simply enter the To and From dates in the cells ($F$2 and $F$1 in our example) and then pull them from their source cells by employing the IF AND formula:

=IF(AND(B2>=$F$1, B2<=$F$2), “X”, “”)

Example 4-2

Excel IF AND Function – Example #5

Other than returning values that are predefined, the IF AND function can perform different calculations based on if the mentioned conditions are FALSE or TRUE.

We have a list of students and their test scores as percentages. We now need to grade them. The percentage to grade table is provided below.

percentage criteria
As we can see from the table above, if the percentage is above 90%, then the associated grade for that score is A+. If the percentage score is between 85% and 90%, then the grade is A. If the percentage is between 80% and 85%, then the grade is B+. If the percentage is between 75% and 80%, then the grade is B. If the percentage is between 70% and 75%, then the grade is C+. If the percentage is between 60% to 70%, then the grade is C. If the percentage is between 50% to 60%, then the grade is D+. If the percentage is between 40% to 50%, then the grade is D. And lastly, if the percentage is below 40%, then the grade is F.

Now the list of students and their percentage is shown below:

excel data

The formula we shall use is:

=IF(C2>”90″%,”A+”,IF(AND(C2>”85″%,C2<=”90″%),”A”,IF(AND(C2>=”80″%,C2<=”85″%),”B+”,IF(AND(C2>=”75″%,C2<=”80″%),”B”,IF(AND(C2>=”70″%,C2<=”75″%),”C+”,IF(AND(C2>=”60″%,C2<=”70″%),”C”,IF(AND(C2>=”50″%,C2<=”60″%),”D+”,IF(AND(C2>=”40″%,C2<=”50″%),”D”,”F”))))))))

We have made use of Nested IF and also used several AND in conjunction with them.

IF function evaluates a condition supplied in its argument and returns one output or another based on the result (TRUE or FALSE).

The formula of IF is as follows:

IF syntax

Here Logical_Test is the condition that will get evaluated. Based on the outcome, either Value_If_True will be returned, or else Value_If_False will be returned.

In the first case, i.e. for Student1, we see that the percentage is 74%; thus, the result returned as per the percentage-to-grade table shown above.

Formula for grades

Similarly, for Student 2, we see that the percentage is 76%; thus, the result returned as per the percentage-to-grade table shown above will be B, since the percentage between >75% and 80% get grade B.

By dragging the formula applied in cell D2, other students also get graded similarly.

Grade for all students

Things to Remember About IF AND Function in Excel

  • The IF AND function has a limit and can check up to 255 conditions. This is, of course, only applicable for Excel 2007 and higher. For previous versions of Excel, the AND function was limited to only 30 conditions.
  • IF AND function can handle logical arguments and works even with numbers. But it can’t handle strings. If the arguments provided are characters, then AND returns a #VALUE! error.

AND function error 1

AND function error 2

  • While working with large sets of data, there are high chances that we may be required to check a few different sets of different AND conditions at a time. At times like these, we need to use the nested form of the IF AND formula:

IF(AND(…), output1, IF(AND(…), output2, IF(AND(…), output3,IF(AND(…) output4)))

Nested IF syntax

Recommended Articles

This is a guide to IF AND Function in Excel. Here we discuss how to use IF AND Function in Excel, practical examples, and a downloadable excel template. You can also go through our other suggested articles –

  1. Multiple IFS in Excel
  2. AND Function in Excel
  3. COUNTIF Excel Function
  4. IF VLOOKUP Formula in Excel
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
Excel for Marketing Training (8 Courses, 13+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formulas, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download IF AND Function Excel Template

EDUCBA

Download IF AND Function Excel Template

EDUCBA

डाउनलोड IF AND Function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more