If you need to test a condition, then take one action if the condition is TRUE, and another action if the condition if FALSE, you can use the IF function.
In the example shown, we have a list of T-shirts that includes color and size. However, the size is abbreviated as "S" for small and "L" for large. Lets 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".
IF cell D5 equals (contains) the letter "S", return the word "Small", ELSE return the word "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 to handle more conditions
This works fine for two conditions, but what if we have a third condition?, for example, "M" for "Medium"?
In that case, we need to extend the formula with another IF statement. We put the second IF statement, replacing the false result.
In the example below, we've added another size (Medium). The formula we are using in E5 is:
If you want to do something specific when two or more conditions are TRUE, you can use the IF function in combination with the AND function to evaluate conditions with a test, then take one action if the r esult is TRUE, and (optionally) do...
If you want to do something specific when a cell equals this or that (i.e. is equal to X or Y, etc.) you can use the IF function in combination with the OR function to run a test, then take one action if the r esult is TRUE, and (optionally) do...
If you want to do something specific when a cell is NOT this or that (i.e. is NOT equal to X or Y, etc.) you can use the IF function in combination with the NOT and OR functions to run a test, then take one action if the result is TRUE, and (...
If you want to copy cells that contain certain text, you can use a formula that uses the IF function together with the SEARCH and ISNUMBER functions. Once you find a value you're looking for you can copy it to another location, or display a message...
In the code above, T1-T5 represents 5 different logical tests, and R1-R5 represents 5 different results. You can see that each IF function requires it's own set of parentheses. This article describes the Excel nested IF construction. Usually, nested...
The IF function can perform a logical test and return 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 is very versatile. You can use it whenever you need to test a condition and take an action based on the result. You can even combine multiple IF statements to check multiple conditions.
In this video we look at how to combine the IF function with the OR and AND functions to test more than one condition at the same time.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.