1-866-245-5224 sales@keystonelearning.com

Excel 2003: Using the HLOOKUP Function

Excel 2003: Using the HLOOKUP Function

With an HLOOKUP Function, you can enter the data (like our grading scale) into a separate worksheet list and have Excel refer the list when searching for a value to return. This way, you don’t have to type it all into a long, complicated IF Function. HLOOKUP stands for Horizontal Lookup. You use a Horizontal Lookup (or, HLOOKUP) when your lookup list contains your data in rows.

The HLOOKUP Function searches for a value in the top row of a lookup list and returns a value in the same column from another row in the table array.

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

The sample above reflects a HLOOKUP Function. Now, let’s break this down:

HLOOKUP                            =              Name of the Function

Lookup_value                   =              What value are you looking for in the 1st column of your list

Table_array                        =              This is the range of your lookup list

Row_index_num              =              Of the columns in your list, which column contains the data you want the formula to display?

Range_lookup                   =              Either True or False (TRUE = Close Match / FALSE = Exact Match)

Now, let’s put that into action using our grading scale example. I’d like Excel to look at a student’s grade. And, based on the score they’ve received, I’d like Excel to display the corresponding letter grade from the lookup list.

Here’s what my formula would look like in Excel:

=HLOOKUP(Sheet1!B2,Sheet3!E2:CV3,2,FALSE)