Microsoft Office Tutorials and References
In Depth Information
You Could Look It Up
Don’t worry—we’re going to explain all this. First note the constant elements we’ve spoken about
earlier: the equal sign, followed by the function name and an open parenthesis. Second, we see that,
unlike say, COUNT or AVERAGE, something more than just a range is fitted in between the parentheses.
Here four different elements—or arguments , and we’ve spoken about them, too—have creeped in there.
The first—in this case B10—names the cell whose grade is going to be looked up and assessed. That’s
John’s 66. The second argument—K$10:L$14—pinpoints our lookup range itself, and yes, it’s
accompanied by those dollar signs, slipped in before the 10 and the 14—the row segments of two cell
addresses. And why? Because we want to look up all our students’ grades in the same lookup table again
and again, and we intend to copy K$10:L$14 down the column of students without its cell references
The third argument—2—refers to the column in the lookup table containing the “answer”—that is,
John’s alphabetic grade; and so what VLOOKUP does next is this: it takes John’s 66 (in B10) and
compares it to the numeric grades in the first column of the lookup table—that is, the K column. John’s
66 falls between the 65 and the 75 in that column, whereupon VLOOKUP treats it as the lower of these
two values (remember—these are grade thresholds, and John hasn’t reached 75), and it then looks to see
which grade has lined up with 65 in the lookup table’s second column—in this case, D. That’s the 2 in the
third argument. John gets a D, and we can now copy this formula down the C column (using that nifty
fill-handle double-click if we wish, because all the cells in the adjoining B column have data in them).
The fourth argument—TRUE, which would have been assumed by default anyway even if you hadn’t
written it—provides for what’s called an approximate match . It’s this argument which allows VLOOKUP
to assess each numeric grade and find its grade niche, e.g., a 78 falls between the lookup table’s 75 and
Once done, the student grades should read (Figure 3–36):
Figure 3–36. Tough exam!
and of course this process would enable us to assign the grades of 5000 students, too, not just 5.
And note importantly that the lowest grade we’re able to look up in this table is 0 (the entry in K10).
Even if it’s unlikely that any student will score that poorly, you want your table to be able to handle all
contingencies—because had I entered a lowest-possible test score of say, 30, in K10 instead and the
hard-partying John crashes and burns with a 25, that score would yield an error message in his
VLOOKUP. You can’t look up a score below the lowest value in the lookup table.
Let’s demonstrate another instance of VLOOKUP, and then review. Suppose we want to calculate
some income tax obligations (purely hypothetical, you understand). We can draw up this tax lookup
table in cells B8:C18 (Figure 3–37):