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:
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:
See how that works? It looks up 62789, yields .27, and goes on to multiply 62789 by .27, returning:
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):