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.
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.
➤ 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.