Chapter 13: Using Formulas for Conditional Analysis
13
FIGURE 13.4
A different auto-selector model
The new formula is as follows:
=IF(E2="Car",VLOOKUP(E3,E6:F7,2,FALSE),VLOOKUP(E3,E10:F11,2,FALSE))This formula can now be used to return the automobile. The IF condition is the same,
but now a TRUE result looks up the proper value in E6:E7, and a FALSE result looks it up in
E10:F11. You can learn more about VLOOKUP in Chapter 14.
Checking if Condition1 AND Condition2 are met
In addition to nesting conditional functions, they can be evaluated together inside an AND
function. This is useful when two or more conditions need to be evaluated at the same time
to determine where the formula should branch.
Figure 13.5 shows a listing of inventory items, their quantities, and the discount that
applies when they are sold. The inventory items are structured with three sections divided
by hyphens. The first section is the department; the second section determines whether
the item is a part, a subassembly, or a final assembly; and the third condition is a unique
four-digit number. We want to assign a discount of 10 percent only to those items that are
in Department 202 and are final assemblies. All other items have no discount.
=IF(AND(LEFT(B3,3)="202",MID(B3,5,3)="FIN"),10%,0%)The IF function returns 10 percent if TRUE and 0 percent if FALSE. For the condition
argument (the first argument), you need an expression that returns TRUE if both the first
section of the item number is 202 and the second section is FIN. Excel provides the AND
function to accomplish this. The AND function takes up to 255 logical arguments separated
by commas. Logical arguments are expressions that return either TRUE or FALSE. For this
example, we’re using only two logical arguments.