IF AND formula in Excel

The tutorial shows how to use IF together with the AND function in Excel to check multiple conditions in one formula.

Some things in the world are finite. Others are infinite, and the IF function seems to be one of such things. On our blog, we already have a handful of Excel IF tutorials and still discover new uses every day. Today, we are going to look at how you can use IF together with the AND function to evaluate two or more conditions at the same time.

IF AND statement in Excel

In order to build the IF AND statement, you obviously need to combine the IF and AND functions in one formula. Here's how:

IF(AND(condition1condition2,…), value_if_true, value_if_false)

Translated into plain English, the formula reads as follows: IF condition 1 is true AND condition 2 is true, do one thing, otherwise do something else.

As an example, let's make a formula that checks if B2 is "delivered" and C2 is not empty, and depending on the results, does one of the following:

  • If both conditions are TRUE, mark the order as "Closed".
  • If either condition is FALSE or both are FALSE, then return an empty string ("").

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

The screenshot below shows the IF AND function in Excel:
IF AND statement in Excel

If you'd like to return some value in case the logical test evaluates to FALSE, supply that value in the value_if_false argument. For example:

=IF(AND(B2="delivered", C2<>""), "Closed", "Open")

The modified formula outputs "Closed" if column B is "delivered" and C has any date in it (non-blank). In all other cases, it returns "Open":
IF AND formula in Excel

Note. When using an IF AND formula in Excel to evaluate text conditions, please keep in mind that lowercase and uppercase are treated as the same character. If you are looking for a case-sensitive IF AND formula, wrap one or more arguments of AND into the EXACT function as it is done in the linked example.

Now that you know the syntax of the Excel IF AND statement, let me show you what kind of tasks it can solve.

Excel IF: greater than AND less than

In the previous example, we were testing two conditions in two different cells. But sometimes you may need to run two or more tests on the same cell. A typical example is checking if a cell value is between two numbers. The Excel IF AND function can easily do that too!

Let's say you have some sales numbers in column B and you are requested to flag the amounts greater than $50 but less than $100.  To have it done, insert this formula in C2 and then copy it down the column:

=IF(AND(B2>50, B2<100), "x", "")

IF formula to check the 'greater than AND less than' condition

If you need to include the boundary values (50 and 100), use the less than or equal to operator (<=) and greater than or equal to (>=) operator:

=IF(AND(B2>=50, B2<=100), "x", "")

Find values between two numbers, including the boundary values.

To process some other boundary values without changing the formula, enter the minimum and maximum numbers in two separate cells and refer to those cells in your formula. For the formula to work correctly in all the rows, be sure to use absolute references for the boundary cells ($F$1 and $F$2 in our case):

=IF(AND(B2>=$F$1, B2<=$F$2), "x", "")

IF AND formula to flag values between the specified numbers

By using a similar formula, you can check if a date falls within a specified range.

For example, let's flag dates between 10-Sep-2018 and 30-Sep-2018, inclusive. A small hurdle is that dates cannot be supplied to the logical tests directly. For Excel to understand the dates, they should be enclosed in the DATEVALUE function, like this:

=IF(AND(B2>=DATEVALUE("9/10/2018"), B2<=DATEVALUE("9/30/2018")), "x", "")

Or simply input the From and To dates in two cells ($F$1 and $F$2 in this example) and "pull" them from those cells by using the already familiar IF AND formula:

=IF(AND(B2>=$F$1, B2<=$F$2), "x", "")

IF AND formula to find dates that fall within a specified range

IF this AND that, then calculate something

Apart from returning predefined values, the Excel IF AND function can also perform different calculations depending on whether the specified conditions are TRUE or FALSE.

To demonstrate the approach, we will be calculating a bonus of 5% for "Closed" sales with the amount greater than or equal to $100.

Assuming the amount is in column B and the order status in column C, the formula goes as follows:

=IF(AND(B2>=100, C2="closed"), B2*10%, 0)

If the specified conditions are TRUE, then calculate something

The above formula assigns zero to the rest of the orders (value_if_false = 0). If you are willing to give a small stimulating bonus, say 3%, to orders that do not meet the conditions, include the corresponding equation in the value_if_false argument:

=IF(AND(B2>=100, C2="closed"), B2*10%, B2*3%)

IF AND formula to perform different calculations depending on whether the conditions are TRUE or FALSE

Multiple IF AND statements in Excel

As you may have noticed, we have evaluated only two criteria in all the above examples. But there is nothing that would prevent you from including three and more tests in your IF AND formulas as long as they comply with these general limitations of Excel:

  • In Excel 2019 - 2007, up to 255 arguments can be used in a formula, with a total formula length not exceeding 8,192 characters.
  • In Excel 2003 and lower, no more than 30 arguments are allowed, with a total length not exceeding 1,024 characters.

As an example of multiple AND conditions, please consider these ones:

  • Amount (B2) should be greater than or equal to $100
  • Order status (C2) is "Closed"
  • Delivery date (D2) is within the current month

Now, we need an IF AND statement to identify the orders for which all 3 conditions are TRUE. And here it is:

=IF(AND(B2>=100, C2="Closed", MONTH(D2)=MONTH(TODAY())), "x", "")

Given that the 'current month' at the moment of writing was October, the formula delivers the below results:
Multiple IF AND statements in Excel

Nested IF AND statements

When working with large worksheets, chances are that you may be required to check a few sets of different AND criteria at a time. For this, you take a classic Excel nested IF formula and extend its logical tests with AND statements, like this:

IF(AND(…), output1, IF(AND(…), output2, IF(AND(…), output3output4)))

To get the general idea, please look at the following example.

Supposing you want to rate your service based on the shipment cost and estimated time of delivery (ETD):

  • Excellent: shipment cost under $20 and ETD under 3 days
  • Poor: shipment cost over $30 and ETD over 5 days
  • Average: anything in between

To get it done, you write two individual IF AND statements:

IF(AND(B2<20, C2<3), "Excellent", …)

IF(AND(B2>30, C2>5), "Poor", …)

…and nest one into the other:

=IF(AND(B2>30, C2>5), "Poor", IF(AND(B2<20, C2<3), "Excellent", "Average"))

The result will look similar to this:
Nested IF AND statements

More formula examples can be found in Excel nested IF AND statements.

Case-sensitive IF AND function in Excel

As mentioned in the beginning of this tutorial, Excel IF AND formulas do not distinguish between uppercase and lowercase characters because the AND function is case-insensitive by nature.

If you are working with case-sensitive data and want to evaluate AND conditions taking into account the text case, do each individual logical test inside the EXACT function and nest those functions into your AND statement:

IF(AND(EXACT(cell,"condition1"), EXACT(cell,"condition2")), value_if_true, value_if_false)

For this example, we are going to flag orders of a specific customer (e.g. the company named Cyberspace) with an amount exceeding a certain number, say $100.

As you can see in the below screenshot, some company names in column B look the same excerpt the characters case, and nevertheless they are different companies, so we have to check the names exactly. The amounts in column C are numbers, and we run a regular "greater than" test for them:

=IF(AND(EXACT(B2, "Cyberspace"), C2>100), "x", "")

To make the formula more flexible, you can input the target customer name and amount in two separate cells and refer to those cells. Just remember to lock the cell references with $ sign ($G$1 and $G$2 in our case) so they won't change when you copy the formula to other rows:

=IF(AND(EXACT(B2, $G$1), C2>$G$2), "x", "")

Now, you can type any name and amount in the referenced cells, and the formula will flag the corresponding orders in your table:
Case-sensitive IF AND function in Excel

IF OR AND formula in Excel

In Excel IF formulas, you are not limited to using only one logical function. To check various combinations of multiple conditions, you are free to combine the IF, AND, OR and other functions to run the required logical tests. Here is an example of IF AND OR formula that tests a couple of OR conditions within AND. And now, I will show you how you can do two or more AND tests within the OR function.

Supposing, you wish to mark the orders of two customers with an amount greater than a certain number, say $100.

In the Excel language, our conditions are expressed in this way:

OR(AND(Customer1, Amount>100), AND(Customer2, Amount>100)

Assuming the customer names are in column B, amounts in column C, the 2 target names are in G1 and G2, and the target amount is in G3, you use this formula to mark the corresponding orders with "x":

=IF(OR(AND(B2=$G$1, C2>$G$3), AND(B2=$G$2, C2>$G$3)), "x", "")

The same results can be achieved with a more compact syntax:

=IF(AND(OR(B2=$G$1,B2= $G$2), C2>$G$3), "x", "")

IF AND OR formula in Excel

Not sure you totally understand the formula's logic? More information can be found in Excel IF with multiple AND/OR conditions.

That's how you use the IF and AND functions together in Excel. To gain more understanding of the formulas, I invite you to download our sample IF AND Excel workbook. Thank you for reading and see you next week!

You may also be interested in:

27 Responses to "IF AND formula in Excel"

  1. swat says:

    Thanks for your great work you do by helping others..
    I have a unique problem seeking formula for use in Office 2010.

    Problem: Looking for a formula to :- Find matches in any two cells in the same row where.. For example column A has exactly five or 8 digit numbers and column B has only a single digit with result in column c stating match/no match

    Say.. i have two columns in Excel each having numbers.In the first column A i have 5 or 8 number digits exactly. in B i have a single digit. what i need to do is find if the number present in column B, is matching in column A with result in column c stating match/no match i have enjoyed your formula given "Example 2. Find matches in any two cells in the same row
    If you are looking for a way to compare columns for any two or more cells with the same values within the same row, use an IF formula with an OR statement: =IF(OR(A2=B2, B2=C2, A2=C2), "Match", "") " ...... But not helping with my problem Please reply. thanks

  2. Imran says:

    Thanks, svetlana.
    for sharing.

  3. Gib says:

    Hi, good day

    How can I horizontally lookup rate from a table (using index match) with 2 conditions.

    This is my data set
    Name 30-Nov-17 03-Jan-18 01-May-18 31-Oct-18
    Andy 10% 8% 10% 8%
    Sarah 10% 8% 10% 8%
    James 10% 8% 10% 8%
    John 10% 8% 10% 8%

    I want to pick out the rates from the table based on Name and Effective Date. Eg., James / 15-Jan-18 would return "8%".

    Many thanks

  4. Ramesh says:

    Dear Sir,
    I would like count no of cells having specific text for a specific employee with drop down list excluding blank cells.Here we are giving work sheet with data for your reference.

    26 27 28 29 30 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
    1 M.SANKAR G G G G G G G G G G G G A G G G G G A G G G G G
    2 K.MURALI G G G G G G G G G G G G G G G G G G G G G
    WELDER B½ G½
    KALASI B½ G½ B½

    Thanks & regards,

  5. sandeep kelakr says:

    i need a help in excel which can identify 2 different set of line example 1 Tin - Arrow gold - 5 K Seeds - 7400 Rs 1 Pkt - Broccoli Saki - pkt - 510 " this is in single cell but i want to highlight the same pls advice

  6. Mabel says:

    Help, how can I do a formula for the following condition: I have cells B3-D6 which will have an "x" (columns) as reply to four questions (rows). I want the fifth row to automatically add the" x" to be the total of the "X" only if it equals to four or three x's in a column. If a column equals 2 or less, nothing should be done. I would truly appreciate any assistance. Thanks.

  7. Seyha says:

    This is very nice lesion. thank for teaching us. I have a question as below.
    Below is the formulas I use as an example, but I got one error with the latest formulas, please assist to let me know what happened on this or because using wrong.

    =IF(AND(B4<=25000,C4<=45),"Med N1",IF(AND(B4=46),"Med N3",IF(AND(B4=46),"Med N4",IF(AND(B4>=50001,C4=50001,C4>=46),"Med N5",IF(AND(B4>=100000,C4=100001, C4>=46),"Med N6","")))))))

    I would like to ask why this below condition not apply?

    IF(AND(B4>=100001, C4>=46),"Med N6","")

  8. Kristen says:

    Hi there!! This is really long, but I am looking for a formula that would handle all of this

    If column E = Eagle and column D = Select 6 and column P = 0-75 then column Q = 1.5%
    If column E = Eagle and column D = Select 6 and column P = 76-80 then column Q = 1.1%
    If column E = Eagle and column D = Select 6 and column P = 81-85 then column Q = .75%
    If column E = Eagle and column D = Platinum 5 or Platinum 7 and column P = 0-75 then column Q = .75%
    If column E = Eagle and column D = Platinum 5 or Platinum 7 and column P = 76-80 then column Q = .50%
    If column E = Eagle and column D = Platinum 5 or Platinum 7 and column P = 81-85 then column Q = .38%

    If column E = Symetra and column D = Edge GPS 5 or Edge GPS 7 then column Q = 1%
    If column E = Symetra and column D = Advantage Income, Custom 5, Custom 7, Select 5 or Select 7 then column Q = .5%

  9. Sedrick says:

    I'm trying to use the IF statement to produce a value for three different cells that are equal to use that equal number. if they are not equal use the lowest value of the three.
    What do I need to use to produce a numeric value for each instance?

  10. maitrey says:

    what is nested if function

  11. Chris says:

    currently have =IF(AND(F3>=D5,F3<=E5),”X”,””). This is checking to see if F3 is between two dates and if so to put an X in the cell otherwise leave blank. I want to add to that formula IF F3 is 1 day past E2 (which is a end date of project), or is 2 days past E2, then put a W. How might I accomplish this?

  12. Kerry says:

    I have the following scenario which I can't seem to get the IF(OR(AND to work. I have an employee database that lists the name, title and location. I am using the above formula to determine security groups and distribution groups. I have come across a scenario where I have multiple titles with multiple possible locations. I currently have the following formula, which comes back with findings but they are inaccurate. =IF(OR(B3="OCT TECH - OCT Technician",B3="TECH - Ophthalmic Technician",B3="SCRIBE - Ophthalmic Scribe",B3="CLMANG - Clinic Manager",B3="OPTAST - Ophthalmic Assistant",B3="VFTECH - Visual Field Technician",B3="SPTEST - Special Testing Technician",B3="SXCORD - Surgical Coordinator",B3="DOCTOR - Medical Doctor",B3="OPTOM - Optometrist")*AND(C2="HAGER - Hagerstown",C2="Fred - Frederick",C2="HANCO - Hancock",C2="MARTI - Martinsburg"),"YES","NO") Any ideas? Thank you

  13. Rich McGill says:

    I have a list of customers each with a unique ID number
    I can have the same customer multiple times on a report
    Each Customer has a status of Ordered or Ordering
    Using the customer number I need to know which customer number has both "Ordered" & "Ordering" in its status field.
    The Result should be a Yes or No.

  14. chris says:

    the moving ribbon on the bottom of the blog page is crap, cant read because of that stupid thing

  15. Helen says:


    I am trying to use match formula to give me the row where a formula applies too 10 cells. Eg if the cell number stays bellow 4.5 for 10 cells then give me the row this happens in. But how would i write 10 cells. As so far i have match(value, array, match type) but i need to put in only if applies to the following 10 cells.

    Any thoughts?

  16. Keith says:

    I've created a conduit fill schedule where if you select a conduit type the conduit type and the conduit size you get the conduit area number associated with that specific conduit type and trade size. In cell G, you can choose the conduit type via a drop down list. In cell F you enter the conduit size. In cell K depending on the selected conduit type (cell G) and the conduit size (cell F) you get the conduit area number for (cell G) & (cell F). My conduit types are PVC 40,PVC 80,RMT,EMT, & LFMC. When I combine IF with Vlookup in cell K the conduit area number is for PVC 40 only, the cell doesn't recognize the other conduit types.
    How do I solve this?
    Thank You for your expertise.

  17. rmahadevan says:

    i want formula for the followings:
    1.ifSalary Rs.7000 =Lessthan than salary <7000*8.33%
    2.7001 to 21000 salary (533.33/31days * no of worked days.
    3.above 21000 =(833.33/31days* no.of.worked days.
    kindly help me.

  18. Mark says:

    I am trying the following formula and i get an error, can you help?

    my input looks like this, in K i would like to see OJ1, when D is OJ and F is 1113. the first part of the nested commands works fine.
    D F K
    OJDS01 1113 OJ1

  19. SUNIL says:

    IF A is greater than 1000 and lesser than 2000 then figure should display of "A" CAN YOU SHARE THE FORMULA FOR THE SAME

  20. Kathryn Wills says:

    Im looking for a formula that works for this:

    I have tried a combination of if, and and or together and =time(7,0.0) and it only returns the first logical test. Im losing sleep over it!

    If cell b6 = yes
    Cell c6 is less than 7am or greater than 3pm
    Return yes value.

  21. Patrick says:

    Can someone tell me what's wrong with this formula, I can't seem to get it to work. Thanks!
    =IF(AND(L11>0, L11=18.5, L11=25, L11=30, L11<100, "Obese"))))))))

  22. Patrick says:

    =IF(AND(L11>=30, L11<100), "OBESE", IF(AND(L11=25), "OVERWEIGHT", IF(AND(L11>25, L11>=18.5), "NORMAL WEIGHT", IF(AND(L110), "UNDERWEIGHT")))))

  23. alisyn says:

    so, for my homework im supposed to do this:
    In cell B10, enter a formula using the IF and AND functions to indicate whether the revenue goal has been met that month:
    a. Enter the logical test using the AND function to determine if the Fundraisers amount in cell B7 equals 0 and the Total in cell B8 is greater than 20000.
    b. If the logical test is true, display Yes (using “Yes” for the value_if_true argument).
    c. If the logical test is false, insert a nested IF function.
    d. Enter the logical test of the nested IF function using the AND function to determine if the Fundraisers amount in cell B7 is greater than 0 and the Total in cell B8 is greater than 200000.
    e. If the logical test for the nested IF function is true, display Yes (using “Yes” for the value_if_true argument).
    f. If the logical test is false, display No (using “No” for the value_if_false argument).

    but i cant get it to work. i tried making this formula:
    but it only gives a result of #NAME?

  24. Michael T says:

    I have 2 scenario's which have 2 diff IF statements. I need this to be in the same cell and cannot get it to work. Any suggestions.

    In both statements if all the AND(.....) are true then it only needs to return "-".


  25. john says:

    Can you please give a correct formula for the below


  26. jennifer says:

    what if im looking for an IF THEN in a range,
    IF in this range "x" is found THEN do this

Post a comment

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Sheila Blanchard