HLOOKUP in Excel
The HLOOKUP function (short for “Horizontal Lookup”) is used to search for a value in the first row of a table and return a value in the same column from a specified row.
Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- lookup_value: The value to search for in the first row.
- table_array: The range of cells that contains the data.
- row_index_num: The row number (in the range) from which to return the value.
- range_lookup: Optional; TRUE for approximate match or FALSE for exact match.
Example: Student Grades Lookup
Sample Data:
| Subject | Math | English | Science | History | Art |
|---|---|---|---|---|---|
| Student A | 85 | 90 | 78 | 88 | 92 |
| Student B | 75 | 85 | 80 | 78 | 88 |
| Student C | 95 | 88 | 92 | 84 | 90 |
Task:
Find the Science score for Student C.
Steps:
Step 1: Use HLOOKUP to Find the Score
- Formula:
=HLOOKUP("Science", A1:F4, 4, FALSE) - Explanation:
- “Science”: The value to look for in the first row.
- A1:F4: The table array containing the data.
- 4: The row number (relative to the table) for Student C.
- FALSE: An exact match is required.
- Result: 92
Key Notes:
- The lookup_value (e.g., “Science”) must be in the first row of the table.
- The row_index_num specifies the row to return a value from (relative to the table range).
- Use FALSE for an exact match (recommended for most use cases).
Additional Example:
Find the Math score for Student B:
- Formula:
=HLOOKUP("Math", A1:F4, 3, FALSE) - Result: 75
