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:
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:
I see I can’t put anything by you. You’ve noticed a new, fourth argument in that formula, and here’s
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: