How the HLOOKUP Function Works
The HLOOKUP function is a type of search function. This function searches for specific information in a worksheet by first finding the specified value in the column labels and searching that column for the corresponding value. The HLOOKUP function is best suited for worksheets with large amounts of data. This example uses a simple worksheet to show how the HLOOKUP function works. In this worksheet, a retailer tracks sales by product and by the channel where each product is sold. Instead of searching the worksheet to find the online sales for cameras, for example, the HLOOKUP function can perform the task.
Syntax of the HLOOKUP Function
The syntax of the HLOOKUP function is: Here’s what each argument does in the HLOOKUP function:
lookup_value (required): The column to be searched. The HLOOKUP function searches the first row to find this value. This argument can be a cell reference or a column label.table_array (required): The table to be searched for the specified data. This can be a reference to a range or a range name.row_index_num (required): The number of the row from which Excel will return data.range_lookup (optional): This argument tells the HLOOKUP function what to do if it doesn’t find an exact match. Argument values are TRUE and FALSE.If the value is TRUE and the table data is sorted from smallest to largest, HLOOKUP returns the largest value that is smaller than the lookup_value argument.If the value if FALSE, the HLOOKUP function returns an error if an exact match isn’t found.
How to Use HLOOKUP in Excel
This example uses the HLOOKUP function to find the online sales for cameras. Here’s how to enter the formula in a worksheet:
How to Use Wildcards With HLOOKUP
When you don’t know the exact text or column name you need, use a wildcard with HLOOKUP. These are the wildcards you can use in Excel to perform a text search:
Asterisk (): Use to indicate that at least one letter is missing from the search term. For example, when searching for a product and you’re not sure if the name is Camera, Cameras, or Camera & Video, enter Camera.Question mark (?): Use to indicate that only one letter is missing from the search term. For example, when searching for a customer and you’re not sure if the name is Petersen or Peterson, enter Peters?n.
