Find Specific Data with the Excel HLOOKUP Function

Young woman peering inside laptop screen
Don't get lost in your spreadsheets with the HLOOKUP function. Tim Robberts / Getty Images

Excel's HLOOKUP function, short for horizontal lookup, can help you find specific information in large data tables such as inventory lists or large membership contact lists.

HLOOKUP works much the same Excel's VLOOKUP function. The only difference is that VLOOKUP searches for data in columns while HLOOKUP searches for data in rows.

01
of 07

Starting the HLOOKUP Function

Screenshot of Excel showing HLOOKUP Formula

Begin by adding the sample data, shown in the picture above, into cells D4 to I5 of your worksheet. It is also a good idea to add headings to the worksheet to show what data is being retrieved by HLOOKUP. For this tutorial enter the following headings into the cells indicated.

The HLOOKUP function and the data it retrieves from the database will be located in cells to the right of these headings:

  • D1: Part Name
  • E1: Price

Creating the Formula

  1. Click on cell E2 to make it the active cell — this is where we will start the HLOOKUP function.
  2. Click on the Formulas tab.
  3. Choose Lookup & Reference from the ribbon to open the function drop down.
  4. Click on HLOOKUP in the list to bring up the Formula Builder.

The data that we enter into the four blank rows in the dialog box will form the arguments of the HLOOKUP function. These arguments tell the function what information we are after and where it should search to find it.

02
of 07

The Lookup Value

Screenshot of Excel showing the Lookup_value

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

The information that HLOOKUP will return 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. Click on the Lookup_value line.
  2. Click on cell D2 to add this cell reference to the Lookup_value line — this is the cell where we will type the part name about which we are seeking information.
03
of 07

The Table Array

Screenshot of Excel showing 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. Click on the Table_array line.
  2. Highlight cells E4 to I5 in the spreadsheet to add this range to the Table_array line.
04
of 07

The Row Index Number

Screenshot of Excel showing The Row Index Num

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

  • If you enter a 1 into the row index number, HLOOKUP returns a value from the first column in table_array.
  • If the row index number is 2, it returns a value from the second row in table_array.
  1. Click on the Row_index_num line.
  2. Type a 2 in this line to indicate that we want HLOOKUP to return information from the second row of the table array.
05
of 07

The Range Lookup

Screenshot of Excel showing the Range_lookup

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

  • If TRUE or if this argument is omitted: HLOOKUP will use 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 will only use 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. Click on the Range_lookup line.
  2. Type the word False in this line to indicate that we want HLOOKUP to return an exact match for the data we are seeking.
  3. Click Done.
  4. If you have followed all the steps of this tutorial you should now have a complete HLOOKUP function in cell E2.
06
of 07

Using HLOOKUP to Retrieve Data

Screenshot of Excel showing HLOOKUP results

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 the ENTER key on the keyboard.

HLOOKUP uses the Row Index Number to determine which item of data should be shown in cell E2.

  1. Click on cell E1 in your spreadsheet.
  2. Type Widgets into cell E1 and press the ENTER key on the keyboard.
  3. The price of widgets should now be displayed in cell E2.

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.

07
of 07

Excel HLOOKUP Error Messages

Screenshot of Excel showing an error in the formula

When working in Excel, things don't always work according to plan. The following error messages are associated with HLOOKUP, and can potentially help you solve any issues you might be experiencing.

#N/A error:

  • This error is displayed if the lookup value is not found in the first column of the table array.
  • It will also be displayed if the range for the table array argument is inaccurate.

#REF!:

  • This error is displayed if the row index number argument is greater than the number of rows in table array.