Microsoft Office Tutorials and References
In Depth Information
You Could Look It Up
You Could Look It Up
Let’s start with VLOOKUP , a function I’ve used countless times to do countless things. The V in
VLOOKUP stands for vertical and points to what’s called a lookup table, a collection of data in which a
value is….looked up.
But that’s terribly abstract. Let’s look at a VLOOKUP example, turning once again to the real-world
domain of exam grading. Suppose I’ve been entrusted with one more batch of grades, to which I assign
numerical scores which must be converted into letter grades. In cells K10 through L14, establish the
scale shown in Figure 3–34:
Figure 3–34. Another lookup table, featuring a grading scale
It’s a rather simple affair, but notice what the table seems to require, and not to require. The table
reads vertically, naturally, and in our case consists of two columns, the first of which records a series of
grade intervals which are arrayed in ascending order and aren’t evenly spaced in equal numeric
intervals—that isn’t required.
The second column enumerates the alphabetic grade equivalents, each one of which represents a
grade threshold . For example, in order to earn a B you need to achieve a minimum score of 85. Score an
84, and you get a C. Score an 84.9, and you get a C. Tough teacher.
Now we’ll enter the scores to be looked up and assigned those alphabet grades. We’ll just work with
five students, so in cells A10:B14 enter (Figure 3–35):
Figure 3–35. A typical lookup table, organized by student name
And it’s in the C column, alongside each student grade, in which we’ll compose our VLOOKUPs.
Click in cell C10 and type:
=VLOOKUP(B10,K\$10:L\$14,2,TRUE)

Search JabSto ::

Custom Search