Microsoft Office Tutorials and References

In Depth Information

**You Could Look It Up**

Figure 3–38.
A three-column lookup table

And we could rewrite John’s VLOOKUP in C10 to read:

=VLOOKUP(B10,K$10:M$14,3,TRUE)

In which case we’d see Barely in that cell.

And what’s different about this rewrite? Two things: the lookup table now spans three columns, and

we’re we’re looking up our “answer”—the item which will appear in C10—in that third column.

One more VLOOKUP permutation: Suppose we wanted to be able to type a student’s name in a

selected cell and be able to immediately determine the numeric grade she earned. That is, if I type

Cynthia I want to see 71 in the next cell, and so on. If so, we could treat our student name/grade list—

A10:B14—as a lookup table. Why not?

And in D10 we could enter any student’s name, and in E10 write:

=VLOOKUP(D10,A10:B14,2,FALSE)

I see I can’t put anything by you. You’ve noticed a new, fourth argument in that formula, and here’s

why.

By default, VLOOKUP requires that the
first
lookup table coluum—the one containing the values to

be looked up and assessed—be arrayed either in ascending numerical or alphabetical order (yes; that

first column can display text). But we see that the first column in our current lookup table—the names of

students—are assuredly
not
in such order. If we don’t want to sort the list—and here we don’t—we can

enter FALSE in the VLOOKUP syntax, which instructs Excel to look for
exact
matches in the first column,

irrespective their order—no more approximate matches. So if I type Cynthia in D10, I
should
see 71 in

E10. If I omit the FALSE, I
won’t
see 71 there. And if I type Barack in D10—a name which doesn’t appear

at all in the lookup table—I’ll get an error message.

Again, project this scenario onto 500 or more students, and you’ll appreciate how swiftly VLOOKUP

can deliver information about any one of them. And before we move on, you’ll want to know that

VLOOKUP has a sibling named
HLOOKUP
, which works in precisely the same way, except its lookup

table runs horizontally, e.g., (Figure 3 - 39):

Figure 3–39.
A horizontal lookup table, for use with HLOOKUP

If the table above has been written in say, E13:O24, an HLOOKUP might look like this: