Microsoft Office Tutorials and References

In Depth Information

**You Could Look It Up**

Figure 3–37.
A lookup table for calculating tax obligation by income level

The table presents a tax schedule, which assesses income in dollars, and the values in the second

column are really percentages. I haven’t formatted either column as currency and percent, respectively,

simply because we haven’t gotten to formatting yet. Thus an income of $32,567 would be assessed at a

rate of .15, or 15%, because that income falls between 30000 and 40000, and again VLOOKUP falls back

to the
lower
of the two and “looks up” the matching figure for 30000 in the
second
column : .15.

In H8 we can enter any income total, say 62789, and in I8 we can write:

=VLOOKUP(A8,B8:C18,2,TRUE)

Our answer: .27, or 27%.

There’s nothing conceptually new in this second case—it’s pure review. VLOOKUP takes the

number in cell H8-62789—and compares it to the values in the first column of the lookup table in

B8:C18. Because the income falls between 60000 and 70000, it’s treated as the former, whereupon 60000

is measured against the same row in the second column—namely, .27.

And where are the dollar signs, you ask? You
could
enter them, and you
would
, if you had a string of

incomes to assess down the H column starting with H8. You’d then copy the original VLOOKUP in I8

down the I column, and yes—here the dollar signs would be most handy indeed, because we’d want all

the incomes to be looked up on the same table.

As usual, there’s more to say about VLOOKUP. For one thing, if you wanted to learn exactly how

much tax a taxpayer actually owes, we could write in cell I8:

=VLOOKUP(A8,B8:C18,2,TRUE)*A8

See how that works? It looks up 62789, yields .27, and goes on to multiply 62789 by .27, returning:

$16,953.03

Once the formatting is applied.

Some other VLOOKUP thoughts: note that our lookup tables to date have comprised two columns.

But nothing prevents us from adding a third and even more columns, which would enable us to achieve

different sets of lookup outcomes.

For example, I could devise this lookup table, if we return to our grading chores (Figure 3 -38):