The HLOOKUP function in Excel is used to search for a value in the top row of a table or array and return a value in the same column from a specified row. HLOOKUP stands for "Horizontal Lookup" and is useful when your data is organized in rows rather than columns.
Syntax
| Function | Syntax |
|---|---|
| HLOOKUP | =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) |
- lookup_value: The value to search for in the top row of the table.
- table_array: The table or array where the data is stored.
- row_index_num: The row number in the table from which to retrieve the value (the first row is 1).
- [range_lookup]: Optional; TRUE for an approximate match, or FALSE for an exact match.
Example
Consider the following table to illustrate the use of the HLOOKUP function:
| Product | Q1 | Q2 | Q3 | Q4 |
|---|---|---|---|---|
| Apples | 500 | 700 | 600 | 800 |
| Bananas | 300 | 400 | 500 | 600 |
| Cherries | 200 | 300 | 400 | 500 |
Steps:
- To find the sales for "Apples" in "Q3", use the formula:
=HLOOKUP("Q3", A1:E4, 2, FALSE). - To find the sales for "Bananas" in "Q4", use the formula:
=HLOOKUP("Q4", A1:E4, 3, FALSE).
Practice Exercise 1
Task: Using the table below, find the sales for "Electronics" in "February".
Practice Exercise 2
Task: Using the table below, find the attendance for "Grade 3" in "Week 2".
Exercise 1 Solution
To find the sales for "Electronics" in "February":
Use the formula: =HLOOKUP("February", A1:E4, 2, FALSE), which results in 1500.
Exercise 2 Solution
To find the attendance for "Grade 3" in "Week 2":
Use the formula: =HLOOKUP("Week 2", A1:E5, 4, FALSE), which results in 28.
By mastering the HLOOKUP function in Excel, you can efficiently search for values within rows and retrieve corresponding data, enhancing your data analysis and reporting capabilities.