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
Ankit Srivastava is an IT trainer, technology educator, and digital skills mentor with expertise in programming, data analytics, AI, and software development. He has successfully trained thousands of learners, with more than 10,000 student enrollments on Udemy. His practical teaching approach empowers students and professionals to build in-demand technical skills. Colorstech channel where Ankit posts video tutorials has more than 8000 Subscribers.
