When the data in your Excel worksheet covers hundreds of columns and dozens of rows, use the HLOOKUP function to find a specified value in a specified column.

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.

Instructions in this article apply to Excel for Microsoft 365, Excel 2019, Excel 2016, and Excel 2013.

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:

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

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:

  • 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.

  • Enter the worksheet data, then organize the column names in ascending order.

  • Select the cell that will display the result of the HLOOKUP function.

  • Select Formulas > Lookup & Reference > HLOOKUP.

  • In the Function Arguments dialog box, place the cursor in the Lookup_value text box.

  • In the worksheet, select the cell that contains the value you want to find in the top row of data.

  • Use a cell reference if you want to search for different values. To search for a different value, enter a different name in the cell.

  • In the Function Arguments dialog box, place the cursor in the Table_array text box.

  • In the worksheet, select the data you want to search. In this example, the entire dataset is selected.

  • In the Function Arguments dialog box, place the cursor in the Row_index_num text box and enter the number of the row that contains the result you want.

  • This is not the row number that appears in the Excel worksheet. This number is the row in the selected array.

  • Select OK.

  • The HLOOKUP function searches the first row to find the lookup_value, and then it searches that column to find the specified value. The value appears in the selected cell.

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:

Enter the worksheet data, then organize the column names in ascending order.

Select the cell that will display the result of the HLOOKUP function.

Select Formulas > Lookup & Reference > HLOOKUP.

In the Function Arguments dialog box, place the cursor in the Lookup_value text box.

In the worksheet, select the cell that contains the value you want to find in the top row of data.

Use a cell reference if you want to search for different values. To search for a different value, enter a different name in the cell.

In the Function Arguments dialog box, place the cursor in the Table_array text box.

In the worksheet, select the data you want to search. In this example, the entire dataset is selected.

In the Function Arguments dialog box, place the cursor in the Row_index_num text box and enter the number of the row that contains the result you want.

This is not the row number that appears in the Excel worksheet. This number is the row in the selected array.

Select OK.

The HLOOKUP function searches the first row to find the lookup_value, and then it searches that column to find the specified value. The value appears in the selected cell.

  • 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.

Add as much information as you can to the wildcard search. Excel only returns one match and doesn’t indicate if there are multiple matches.

Get the Latest Tech News Delivered Every Day