The HLOOKUP (Horizontal Lookup) formula in Excel is used to search for a value in the top row of a table or range and return a value in the same column from a specified row. It is particularly useful when you need to look up data that is organized horizontally (across columns).
Other similar powerful formulas in Excel which you might have worked with are VLOOKUP and XLOOKUP which simplify the process of searching and retrieving data.
In this blog we will explore the HLOOKUP formula.
Syntax of HLOOKUP:
=HLOOKUP(lookup_value, table_array, row_index_num,[range_lookup])
- lookup_value: The value you want to search for in the top row of the table.
- table_array: The range of cells that contains the data (including the row with the lookup value).
- row_index_num: The row number in the table from which you want to retrieve the value. The top row is 1, the second row is 2, and so on.
- range_lookup (optional): A logical value that indicates whether you want an exact match (FALSE) or an approximate match (TRUE).
Step-by-Step Guide to Using HLOOKUP:
Example:
Suppose you have the following table, and you want to look up the sales value for “Product C” in the third quarter:
| Q1 | Q2 | Q3 | Q4 | |
| A | 500 | 700 | 900 | 850 |
| B | 600 | 650 | 850 | 950 |
| C | 700 | 800 | 950 | 900 |
You want to retrieve the sales figure for Product C in Q3.
Steps:
- Select the cell where you want the result.
- Type the formula:
=HLOOKUP(“Q3”, A1:E4, 3, FALSE)
- “Q3”: This is the value you’re looking for in the top row.
- A1
: This is the table range that includes the headers and data. - 3: This is the row index for Product C (since Product C is in the 3rd row of the table).
- FALSE: This indicates that you want an exact match for “Q3” in the top row.
- Press Enter. The result will be 950, which is the sales value for Product C in Q3.
Detailed Explanation of Parameters:
- lookup_value: Can be a specific value or a reference to a cell containing the value. For instance, instead of using “Q3”, you could reference a cell like B1 if B1 contains “Q3”.
- table_array: The range must include the top row where you are looking for the lookup_value and the rows from which you want to retrieve data. Make sure the top row contains unique values.
- row_index_num: This is the row number in the table_array that contains the value you want to return. For example, if you want to return data from the second row under the header, you would use 2.
- range_lookup:
- TRUE (default): Will find an approximate match. Useful when the data is sorted.
- FALSE: Will find an exact match. It’s often safer to use this option to avoid incorrect results.
Example with Approximate Match:
Suppose you have the following data and want to find the approximate grade for a score of 85:
| 60 | 70 | 80 | 90 | 100 | |
| Grade | D | C | B | A | A+ |
The formula:
=HLOOKUP(85, B1:F2, 2, TRUE)
- Looks for 85 in the first row.
- As there is no exact match, it will find the closest smaller value (which is 80).
- The result will be B, the grade corresponding to 80.
Common Errors in HLOOKUP:
- #N/A: If the lookup_value is not found, or if you’re looking for an exact match and one doesn’t exist, you will get this error.
- #VALUE!: Occurs if the row_index_num is less than 1 or greater than the number of rows in the table.
- #REF!: Happens when the row_index_num exceeds the number of rows in the table_array.
Conclusion:
Excel sheets are versatile tools that help users organize, analyze, and present data efficiently. For those just starting out, enrolling in an Excel training course for beginners can be invaluable, as it teaches essential skills for working with Excel sheets, formulas, and data analysis tools.The HLOOKUP formula is a powerful tool when dealing with data organized horizontally. By understanding the parameters and using it effectively, you can easily look up values and retrieve important information from large datasets.
