Find Specific Data with the Excel HLOOKUP Function

Don't get lost in your spreadsheets with the HLOOKUP function
Tim Robberts / Getty Images

Excel's HLOOKUP function, short forĀ horizontal lookup, finds specific information in large data tables such as inventory lists or membership contact lists. HLOOKUP works similar to Excel's VLOOKUP function. The only difference is that VLOOKUP searches for data in columns while HLOOKUP searches for data in rows.

The instructions in this article apply to Excel 2019, 2016, 2013, 2010; Excel for Office 365, Excel Online, Excel for Mac, Excel for iPad, Excel for iPhone, and Excel for Android.

Enter Data for the HLOOKUP Function

To follow along with this tutorial, add the sample data, shown in the picture below, into cells D1 to I5 of a blank worksheet.

A screenshot showing tutorial data for the Excel HLOOKUP function

Headings are added to the worksheet to show what data is being retrieved by HLOOKUP. The retrieved data will be located in the cells to the right of these headings.

Create the HLOOKUP Formula

Here's how to create the HLOOKUP formula:

  1. Select cell E2 to make it the active cell. This is where the HLOOKUP function will be located.

  2. Select Formulas.

  3. Select Lookup & Reference to open the function drop-down list.

  4. Choose HLOOKUP to open the Function Arguments dialog box. In Excel for Mac, the Formula Builder opens.

The data entered into the four blank rows in the dialog box form the arguments of the HLOOKUP function. These arguments tell the function what information to find and where it should search.

Enter the Lookup Value

The first argument is the Lookup_value. The Lookup_value tells HLOOKUP which item in the database you are seeking information. The Lookup_value is located in the firstĀ row of the selected range.

The information that HLOOKUP returns is always from the same column of the database as the Lookup_value. The Lookup_value can be a text string, a logical value (TRUE or FALSE only), a number, or a cell reference to a value.

  1. Place the cursor in the Lookup_value text box.

  2. Select cell E1 to add this cell reference to the Lookup_value text box.

    A screenshot showing how to enter the Lookup_value for the HLOOKUP function in Excel
  3. Cell E1 is where the part name you are seeking information will be entered.

Enter the Table Array

The Table_array argument is the range of data that the HLOOKUP function searches to find your information. The Table_array must contain at least two rows of data with the first row containing the Lookup_value.

The Table Array range does not need to include all rows or even the first row of a database.

  1. Place the cursor in the Table_array text box.

  2. Highlight cells E4 to I5 in the worksheet.

    A screenshot showing how to enter the table array for the HLOOKUP function in Excel
  3. The array appears in the Table_array text box.

Enter the Row Index Number

The row index number argument (Row_index_num) indicates which row of the Table_array contains the data you want. Here are a few examples of different data entered into the argument:

  • If Row_index_num is 1, HLOOKUP returns a value from the first column in table_array.
  • If Row_index_num is 2, HLOOKUP returns a value from the second row in table_array.
  1. Place the cursor in the Row_index_num text box.

  2. Enter 2.

    A screenshot showing how to enter the Row_index_num argument for the HLOOKUP function in Excel
  3. This tells HLOOKUP to return information from the second row of the table array.

Enter the Range Lookup

The Range_lookup argument is a logical value (TRUE or FALSE only) that indicates whether HLOOKUP should find an exact or an approximate match to the Lookup_value.

  • If TRUE or if this argument is omitted, HLOOKUP uses an approximate match if it cannot find an exact match to the Lookup_value. If an exact match is not found, HLOOKUP returns the next largest value that is less than the Lookup_value.
  • If FALSE, HLOOKUP only uses an exact match to the Lookup_value. If there are two or more values in the first column of Table_array that match the Lookup_value, the first value found is used.

If an exact match is not found, a #N/A error is returned.

  1. Place the cursor in the Range_lookup text box.

  2. Enter FALSE to tell HLOOKUP to return an exact match for the data.

    A screenshot showing how to enter the Range_lookup for the HLOOKUP function in Excel
  3. Select OK. Except in Excel for Mac, where you select Done.

  4. A complete HLOOKUP function appears in cell E2.

Use HLOOKUP to Retrieve Data

Once the HLOOKUP function has been completed, it can be used to retrieve information from the database. To do so, type the name of the item you wish to retrieve into the Lookup_value cell and press Enter.

HLOOKUP uses the Row Index Number to determine which item of data should be shown in cell E2. Select cell E1, enter Widgets, and press Enter. The price of widgets displays in cell E2.

A screenshot showing how to use the HLOOKUP function in Excel to retrieve data

Test the HLOOKUP function further by typing other parts names into cell E1 and comparing the data returned in cell E2 with the prices listed in cells E5 to I5.

Excel HLOOKUP Error Messages

Error messages associated with HLOOKUP help you solve any issues you might be experiencing. These are the error messages you may encounter:

  • #N/A error: This error displays if the lookup value is not found in the first column of the table array. It also displays if the range for the table array argument is inaccurate.
  • #REF!: This error displays if the row index number argument is greater than the number of rows in table array.