Find Specific Data with Excel HLOOKUP

Find Specific Information with Excel's HLOOKUP Function
Find Specific Information with HLOOKUP. © Ted French

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

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

Following the steps in the tutorial topics below walk you through using the HLOOKUP function to find specific information in an Excel database.

The last step of the tutorial covers error messages that commonly occur with the HLOOKUP function.

Tutorial Topics

  • Entering Tutorial Data
  • Starting the HLOOKUP Function
  • The Lookup Value
  • The Table Array
  • The Row Index Number
  • The Range Lookup
  • Using HLOOKUP to Retrieve Data
  • Common Error Messages for Excel HLOOKUP
01
of 09

Entering the Tutorial Data

How to Use HLOOKUP in Excel
How to Use HLOOKUP in Excel. © Ted French

When entering the data into an Excel worksheet, there are some general rules to follow:

  1. Whenever possible, don't leave blank rows or columns when entering your data.
  • Leaving blank rows and columns in data tables can make it difficult to use a number of Excel's functions - including HLOOKUP.
  • Enter your data in rows.
  • When laying out your worksheet, list the names describing the data in the first row of the table and, to the beneath that, the data itself.
  • If there is more than one data series, list them one after the other in rows with the title for each data series in the first cell at the left.

For this tutorial

  1. Enter the data as seen in the image above into cells D4 to I5.
  • The first row of data (row 4) contains the part names. The second row (row 5) the price of each part.
02
of 09

Starting the HLOOKUP Function

How to Use HLOOKUP in Excel
How to Use HLOOKUP in Excel. © Ted French

Before starting the HLOOKUP function it is usually 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.

  1. D1 - Part Name
    E1 - Price

Although it is possible to just type the HLOOKUP function into a cell in a worksheet, many people find it easier to use the function's dialog box.

For This Tutorial

  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 list.
  4. Click on HLOOKUP in the list to bring up the function's dialog box.

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.

03
of 09

The Lookup Value

Adding the Lookup Value Argument
Adding the Lookup Value Argument. © Ted French

The first argument is the Lookup_value. It tells HLOOKUP about 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.

For this tutorial

  1. Click on the Lookup_value line in the dialog box
  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.
04
of 09

The Table Array

Adding the Table Array Argument
Adding the Table Array Argument. © Ted French

The Table_array argument is the range of data that the HLOOKUP function searches to find your information. Note that this range does not need to include all rows or even the first row of a database.

The Table_array must contain at least two rows of data though, with the first row containing the Lookup_value (see previous step).

If you enter cell references for this argument it is a good idea to use absolute cell references. Absolute cell references are denoted in Excel by the dollar sign ( $ ). An example would be $E$4.

If you don’t use absolute references and you copy the HLOOKUP function to other cells, there is a chance you will get an error messages in the cells to which the function is copied.

For this tutorial

  1. Click on the Table_array line in the dialog box.
  2. Highlight cells E4 to I5 in the spreadsheet to add this range to the Table_array line. This is the range of data that HLOOKUP will search.
  3. Press the F4 key on the keyboard to make the range absolute ($E$4 : $I$5).
05
of 09

The Row Index Number

Adding the Row Index Number Argument
Adding the Row Index Number Argument. © Ted French

The row index number argument ( Row_index_num ) indicates which row of the Table_array contains the data you are after.

For example:

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

For this tutorial

  1. Click on the Row_index_num line in the dialog box
  2. Type a 2 in this line to indicate that we want HLOOKUP to return information from the second row of the table array.
06
of 09

The Range Lookup

Adding the Range Lookup Argument
Adding the Range Lookup Argument. © Ted French

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.

For This Tutorial

  1. Click on the Range_lookup line in the dialog box
  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 OK to close the dialog box.
  4. If you have followed all the steps of this tutorial you should now have a complete HLOOKUP function in cell E2.
07
of 09

Using HLOOKUP to Retrieve Data

Retrieving Data with the Finished HLOOKUP Function
Retrieving Data with the Finished HLOOKUP Function. © Ted French

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.

For This Tutorial

  1. Click on cell E1 in your spreadsheet.
  2. Type Bolt into cell E1 and press the ENTER key on the keyboard.
  3. The price of a bolt - $1.54 - should 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.
08
of 09

Excel HLOOKUP Error Messages

Excel HLOOKUP Error Messages
Excel HLOOKUP Error Messages. © Ted French

The following error messages are associated with HLOOKUP.

#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. If this argument includes empty rows above the table array.

#REF!:

  • This error is displayed if the row index number argument is greater than the number of rows in table array. In the image above, the #REF! error occurs because the row index number is set to 3 while there are only two rows in the Table_array.

This completes the tutorial on creating and using the HLOOKUP function in Excel 2007.

09
of 09

Example Using Excel 2007's HLOOKUP Function

Enter the following data into the cells indicated:

Cell Data

  • D3 - Part
  • E3 - Bearing
  • F3 - Bolt
  • G3 - Cog
  • H3 - Gear
  • I3 - Washer
  • D4 - Price
  • E4 - $17.34
  • F4 - $1.54
  • G4 - $20.21
  • H4 - $23.56
  • I4 - $1.43

Click on cell E1 - the location where the results will be displayed.

Click on the Formulas tab.

Choose Lookup & Reference from the ribbon to open the function drop down list.

Click on HLOOKUP in the list to bring up the function's dialog box.

In the dialog box, click on the Lookup _value line.

Click on cell D1 in the spreadsheet. This is where we will type the name of the part we wish to price.

In the dialog box, click on the Table_array line.

Highlight cells E3 to I4 in the spreadsheet to enter the range into the dialog box. This is the range of data we want HLOOKUP to search.

In the dialog box, click on the Row_index_num line.

Type the number 2 to indicate that the data we want returned is in row 2 of the table_array.

In the dialog box, click on the Range_lookup line.

Type the word False to indicate that we want an exact match for our requested data.

Click OK.

In cell D1 of the spreadsheet, type the word bolt.

The value $1.54 should appear in cell E1 displaying the price of a bolt as indicated in the table_array.

If you click on cell E1, the complete function = HLOOKUP ( D1 , E3 : I4 , 2 , FALSE ) appears in the formula bar above the worksheet.

Was this page helpful?