Part II: Working with Formulas and Functions
Lookup array: The single column or single row to look in
Match type: For exact matches only, set this argument to FALSE or 0
The value we want to match is the state in cell G4, and we’re looking for it in the range
C3:C25, our list of states. MATCH looks down the range until it finds NH. It finds it in the
12th position, so 12 is used by INDEX as the row argument.
With MATCH computed, INDEX now has all it needs to return the right value. It goes to the
12th row of the range and gets the value from either the first column (for City) or the
third column (for Store #).
If you pass INDEX a row number that is more rows than are included in the range or a column number that is more
columns than are included in the range, INDEX returns the #REF! error.
Looking up values horizontally
If the data is structured in such a way that your lookup value is in the top row rather than
the first column and you want to look down the rows for data rather than across the col-
umns, Excel has a function just for you.
Figure 14.4 shows a table of cities and their temperatures. The user will select a city from a
drop-down box, and you will return the temperature to the cell just below it.
=HLOOKUP(C5,C2:L3,2,FALSE)
FIGURE 14.4
A table of cities and temperatures
The HLOOKUP function has the same arguments as VLOOKUP. The H in HLOOKUP stands for
“horizontal,” while the V in VLOOKUP stands for “vertical.” Instead of looking down the
first column for the lookup_value argument, HLOOKUP looks across the first row. When it
finds a match, it returns the value from the second row of the matching column.
