You can use the HLOOKUP function to find a value in one row of a range of related values. In the range, information in any given column provides all of that particular type of information.
The HLOOKUP function horizontally searches the top row of the range to find a value that you specify. When the HLOOKUP function finds the value in the top row, it searches down that column for one of the values in the column; you use one of the arguments of the HLOOKUP function to identify the row containing the value you want to find. For example, suppose that you have stored students’ numeric grades in a worksheet and you would like to translate numeric grades to the letters A, B, C, D, and F. You can use the HLOOKUP function to quickly find the letter grade corresponding to a number grade.
The HLOOKUP function takes three arguments: First, you identify the cell containing the value for which you want to search. Second, you identify the range containing the data that Excel should search. Third, you identify the row containing the information
you want Excel to display.
1 In your worksheet, list in rows the values that Excel should
search. Note: Be sure to sort the information based on the first row.
2 Click the cell in which you want to store the function.
3 Click the Formulas tab.
4 Click Insert Function. The Insert Function dialog box appears.
5 Type a description here of the function you want to use. This example uses horizontal lookup.
6 Click Go.
7 Click HLOOKUP.
• A description of the selected function appears here.
8 Click OK. dialog box appears.
9 Click here and click the cell containing the search value.
The selected cell address appears in the Lookup_value field.
10 Click here and select the range containing all of the values you want to search.
The selected range appears in the Table_array field.
11 Type the number of the row containing the value you want to find.
Note: The top row is 1.
12. Click OK.
• Excel displays the result of the formula in the cell you selected in Step 2 as a serial number.
• The formula appears in the Formula bar.
TIPS
Why did Excel return a #NAME! error for an HLOOKUP function that I set up?
You need to remember that the order of the information in the range that you search is important. The information in the table must appear from lowest to highest, or, if you are searching text, the text entries must appear alphabetically. In addition, the entries in the top row of the table must be unique. Otherwise, Excel returns an error.
Can I use a cell reference instead of an HLOOKUP function?
Yes, you can. However, using a cell reference does not account for changes that you make to the value that you would have used for the Lookup_value argument. If you make a change to that cell, Excel does not update the cell containing the cell reference — the one in which you would have stored an HLOOKUP function.
I saw the LOOKUP function in the Insert Function dialog box; how does it work?
The LOOKUP function works on a single row or single column range where the values are sorted in ascending or alphabetical order. You supply a search value, a single row or column range to search, and a single row or column range where Excel can store the values it finds. The two single row or column ranges must be the same size.
No Comment