MS Excel: How to use the IF Function with the AND Function

This Excel tutorial explains how to use the Excel IF function and the AND function together with syntax and examples.

Description

The IF function can be combined with the AND function to allow you to test for multiple conditions. When using the AND function, all conditions within the AND function must be TRUE for the condition to be met.

Subscribe

---

If you want to follow along with this tutorial, download the example spreadsheet.

Download Example

Syntax

The syntax for the IF function with the AND function in Microsoft Excel is:

IF( AND( condition1, [condition2], ... ), value_if_true, [value_if_false] )

Parameters or Arguments

condition1, condition2, ...

The conditions to test. There must be at least 1 condition entered in the AND function and you can have up to 30 conditions.

value_if_true

It is the value that is returned if all conditions in the AND function evaluate to TRUE.

value_if_false

Optional. It is the value that is returned if any of the conditions in the AND function evaluate to FALSE.

Returns

Returns value_if_true when all of the conditions in the AND function are TRUE.
Returns value_if_false when any of the conditions in the AND function are FALSE.
Returns FALSE if the value_if_false parameter is omitted and any of the conditions in the AND function are FALSE.

Applies To

• Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Example (as Worksheet Function)

Let's explore how to use the IF function with the AND function in Microsoft Excel.

Based on the spreadsheet above, you can combine the IF function with the AND function as follows:

=IF(AND(A2="Anderson",B2>80), "MVP", "regular")
Result: "MVP"

=IF(AND(B2>=80,B2<=100), "Great Score", "Not Bad")
Result: "Great Score"

=IF(AND(B3>=80,B3<=100), "Great Score", "Not Bad")
Result: "Not Bad"

=IF(AND(A2="Anderson",A3="Smith",A4="Johnson"), 100, 50)
Result: 100

=IF(AND(A2="Anderson",A3="Smith",A4="Parker"), 100, 50)
Result: 50

In the examples above, all conditions within the AND function must be TRUE for the condition to be met.