Microsoft Office Tutorials and References

In Depth Information

**Excel Functions for Your Data Model**

The HLookup function

The HLOOKUP function is the less popular cousin of the VLOOKUP function. The H in HLOOKUP

stands for horizontal. Because Excel data is typically vertically oriented, most situations require a

vertical lookup (or VLOOKUP). However, some data structures are horizontally oriented, requiring a

horizontal lookup; thus the HLOOKUP function comes in handy. The HLOOKUP searches a lookup table to

find a single value from a row of data where the column label matches a given criterion.

HLOOKUP basics

Figure 11-10 demonstrates a typical scenario where HLOOKUP formulas are used. The table in C3

requires quarter-end numbers (March and June) for 2012. The HLOOKUP formulas use the column

labels to find the correct month columns and then locate the 2012 data by moving down the

appropriate number of rows. In this case, 2012 data is in row 4, so the number 4 is used in the formulas.

Figure 11-10:
HLOOKUP formulas help find March and June numbers from the lookup table.

To get your mind around how this works, take a look at the basic syntax of the HLOOKUP function.

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

➤
lookup_value:
The value that you want to look up. In most cases, these values are column

names. In the example in Figure 11-10, the column labels are being referenced for the lookup_

value. This points the HLOOKUP function to the appropriate column in the lookup table.

➤
table_array:
The range that contains the lookup table. In Figure 11-10, that range is B9:H12.

Like the VLOOKUP examples earlier in this chapter, the references used for this argument are

absolute, which means the column and row references are prefixed with dollar ($) signs — as

in $B$7:$H$10. This ensures that the reference doesn’t shift while you copy the formula

down or across.

➤
row_index_num:
The row number that contains the value that you’re looking for. In the

example in Figure 11-10, the 2012 data is located in row 4 of the lookup table. Therefore, the

formulas use the number 4.

➤
range_lookup:
You can specify whether you’re looking for an exact match or an

approximate match. If an exact match is needed, enter
FALSE
for this argument. If the closest match

will do, enter
TRUE
or leave the argument blank.