How to Use Excel's HLOOKUP Function GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office

How to Use Excel's HLOOKUP Function

Quickly search and retrieve data by row

By Coletta Teske Whitehead Coletta Teske Whitehead Writer Central Washington University Coletta Teske is a Lifewire writer focusing on consumer electronics. She has 30 years' experience writing for Adobe, Boeing, Microsoft, and many others. lifewire's editorial guidelines Published on April 6, 2020 Tweet Share Email Tweet Share Email

In This Article

Expand Jump to a Section How the Function Works Syntax Step-by-Step: Use HLOOKUP Use Wildcards 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. Instructions in this article apply to Excel for Microsoft 365, Excel 2019, Excel 2016, and Excel 2013.

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: 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: 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: 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. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Count Data in Selected Cells With Excel's COUNTIF Function How to Create an Excel Lookup Formula With Multiple Criteria How to Use the Round Function in Excel How to Round Numbers Down in Excel With the ROUNDDOWN Function How to Combine the ROUND and SUM Functions in Excel How to Use Excel's MROUND Function Excel SUMIFS: Sum Only Values Meeting Multiple Criteria Use Excel's EOMONTH Function to Add or Subtract Months Finding the Location of Data With Excel's MATCH Function How to Find Data with VLOOKUP in Excel Round up Numbers in Excel With the ROUNDUP Function How to Use the XLOOKUP Function in Excel How to Use the Excel INDEX Function Excel SUM and OFFSET Formula How to Create an Excel Left Lookup Formula Using VLOOKUP How to Use the Excel TRUNC Function Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies