Checking for Multiple Conditions in Excel - dummies

Checking for Multiple Conditions in Excel

Simple conditions can be strung together. This is known as nesting functions. The value_if_true and value_if_false arguments can contain simple conditions of their own. This allows you test more than one condition where subsequent conditions are dependent on the first one.

The figure shows a spreadsheet with two user input fields for the type of automobile and a property of that automobile type. The properties are listed in two ranges below the user input fields. For this example, when the user selects the type and property, you want a formula to report whether the user has identified a coupe, a sedan, a pickup, or an SUV, as follows:

image0.jpg

=IF(E2="Car",IF(E3="2-door","Coupe","Sedan"),IF(E3="Has Bed","Pickup","SUV"))

With some conditional analysis, the result of the first condition causes the second condition to change. In this case, if the first condition is Car, the second condition is 2-door or 4-door. But if the first condition is Truck, the second condition changes to either Has Bed or No Bed.

The data validation in cell E3 changes to allow only the appropriate choices based on the first condition.

Excel provides the IF function to perform conditional analyses. You can also nest IF functions — that is, use another IF function as an argument to the first IF function — when you need to check more than one condition.

In this example, the first IF checks the value of E2. Rather than return a value if TRUE, the second argument is another IF formula that checks the value of cell E3. Similarly, the third argument doesn’t simply return a value of FALSE, but contains a third IF function that also evaluates cell E3.

Here, the user has selected “Truck”. The first IF returns FALSE because E2 doesn’t equal “Car” and the FALSE argument is evaluated. In that argument, E3 is seen to be equal to “Has Bed” and the TRUE condition (“Pickup”) is returned. If the user had selected “No Bed”, the FALSE condition (“SUV”) would have been the result.

In Excel versions prior to 2007, you can only nest functions up to seven levels deep. Starting in Excel 2007, that limit was increased to 64 levels. As you can imagine, even seven levels can be hard to read and maintain. If you need more than three or four levels, it’s good idea to investigate other methods.