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