Excel Tutorials: How to use HLOOKUP function

Excel's HLOOKUP function, which stands for horizontal lookup, is used to find specific information that has been stored in rows in a spreadsheet table.

For more Excel tips and tricks go to my static page:  Excel Tutorials and Tricks Series.


Note: The data in the table should be sorted in ascending order (alphabetically A to Z).

The syntax for the HLOOKUP function is:
= HLOOKUP ( lookup_value , table_array , row_index_num , range_lookup )

lookup _value: The value you want to find in the first column of the table_array .

table_array: The range of data that HLOOKUP searches to find your information. The table_array must contain at least two columns of data. The first column contains the lookup_values.

row_index_num: The number of the row in the table_array that contains the data you want returned.

range_lookup: A logical value (TRUE or FALSE only) that indicates whether you want VLOOKUP to find an exact or an approximate match to the lookup_value. Typing False will return exact matches only.

Example Using Excel 2007's HLOOKUP Function:

Note: For help with this example, see the image above.

1. 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
   
   
2. Click on cell E1 - the location where the results will be displayed.
   
   
3. Click on the Formulas tab.
   
   
4. Choose Lookup & Reference from the ribbon to open the function drop down list.
   
   
5. Click on HLOOKUP in the list to bring up the function's dialog box.
   
   
6. In the dialog box, click on the Lookup _value line.
   
   
7. Click on cell D1 in the spreadsheet. This is where we will type the name of the part we wish to price.
   
   
8. In the dialog box, click on the Table_array line.
   
   
9. Drag select 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.
   
   
10. In the dialog box, click on the Row_index_num line.
    
    
11. Type the number 2 to indicate that the data we want returned is in row 2 of the table_array.
    
    
12. In the dialog box, click on the Range_lookup line.
    
    
13. Type the word False to indicate that we want an exact match for our requested data.
    
    
14. Click OK.
    
    
15. In cell D1 of the spreadsheet, type the word bolt.
    
    
16. The value $1.54 should appear in cell E1 displaying the price of a bolt as indicated in the table_array.
    
    
17. If you click on cell E1, the complete function = HLOOKUP ( D1 , E3 : I4 , 2 , FALSE ) appears in the formula bar above the worksheet.