Lookup
Finding a value under a header
Definition
Looking up a key in the top row and returning a value from a row below means giving HLOOKUP the key, the table, the row number, and FALSE.
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)
Arguments
| Argument | Meaning |
|---|---|
lookup_value |
The key to find in the first row of the table, in double quotes when it is text. |
table_array |
The table that is searched. The key row must come first. |
row_index_num |
The row of the table to return. 2 is the row under the keys. |
range_lookup |
Use FALSE for an exact match. |
Worked example
A size chart stores labels across A2:C2 and counts across A3:C3.
| A | B | C |
|---|---|---|
| S | M | L |
| 4 | 12 | 7 |
Formula: =HLOOKUP("M",A2:C3,2,FALSE)
Result: 12. M is the second key, and the row under it holds 12.
Practice
Cells A2:C2 hold Q1, Q2, and Q3. Cells A3:C3 hold 11, 19, and 7. In D2, write an exact-match formula that returns the number under Q2.
- In D2, write an exact-match HLOOKUP that returns the value for Q2 from A2:C3.
- D2 should show the value for Q2.
Related functions
HLOOKUP is the function that finds the key in the top row and returns the value from the row you name.
Common mistake
Leaving off FALSE: the default is approximate matching, which on unsorted keys can return a nearby column instead of 12.