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)