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.
- Enter the following data into the cells indicated:
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.
- 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.
- 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.