In Excel, you can use IF with AND function to return a value based on two different numbers. Used to evaluate conditions with a test, then take one action if the result is true, else return the result based on the condition.
Ah! why not Nested IF? Anyone who’s spent time in Data Analysis especially in the area of Finance knows that using Nested IF would be a confusing and you may end up getting incorrect result also, it becomes complex. Combining IF with AND function can be a good way in keeping the formula simpler and easy to understand.
IF combined with AND Function
In this section, learn how to build an IF statement with AND. For example, if you are looking for a formula that will go into to cell C2 and, if the number is between 100 and 999 then the result will be 100 – otherwise, if it is outside that range then the result will be zero.
Example of how to use the formula:
Step 1: Put the number in cell C6 you want to test (150).
Step 2: Put the criteria in cells C8 and C9 (100 and 999).
Step 3: Put the results if true or false in cells C11 and C12 (100 and 0).
Step 4: type the formula =IF(AND(C6>=C8,C6<=C9),C11,C12)
Final result
Here is a screenshot in Excel after using the formula for an IF statement between two numbers. You can see the result from the example is 100 because the number 150 is between 100 and 999.
