Part II: Working with Formulas and Functions
FIGURE 14.2
A simplified paystub form
All the VLOOKUP functions in this example have FALSE as the final argument. A FALSE in the match argument tells
VLOOKUP to return a value only if it finds an exact match. If it doesn’t find an exact match, VLOOKUP returns N/A#.
Figure 14.7, later in this chapter, shows an example of using TRUE to get an approximate match.
The other formulas also use VLOOKUP with a few twists. The address and insurance formu-
las work just like the employee name formula, but they pull from a different column. The
pay formula uses two VLOOKUPs; one is divided by the other. The employee’s annual pay is
pulled from the fifth column, and that is divided by the frequency from the fourth column,
resulting in the pay for one paystub.
The retirement formula pulls the percentage from the eighth column and multiplies that by
the gross pay to calculate the deduction. Finally, the taxes formula deducts both insurance
and retirement from gross pay and multiplies that by the tax rate, found with VLOOKUP
pulling from the sixth column.
Of course, payroll calculations are a little more complex than this, but once you understand
how VLOOKUP works, you can build ever more complex models.
Looking up an exact value based on any lookup column
Unlike the table used in Figure 14.1, not all tables have the value that you want to look up
in the leftmost column. Fortunately, Excel provides some functions for returning values
that are to the right of the value you’re looking up.
Figure 14.3 shows the locations of our stores by city and state. We want to return the city
and store number when the user selects the state from a drop-down list.
City: =INDEX(B3:D25,MATCH(G4,C3:C25,FALSE),1)
Store: =INDEX(B3:D25,MATCH(G4,C3:C25,FALSE),3)
