Syntax: =hlookup(lookup_value, table_array, row_index_number, [range_lookup])

Hlookup stands for ‘horizontal lookup’ and can be used to return a value from a data table or array. This function looks for a value in the top row of a table and returns the value in the same column from a specified row.

The function can be broken down into four parts:

  1. lookup_value. What do you want to look up?
  2. table_array. Where do you want to look it up?
  3. row_index_number. In the table_array, specified above, which row do you want to return? The first row in the table is considered 1.
  4. [range_lookup]. The brackets signify an optional argument.  This value can be set to TRUE or FALSE. FALSE returns an exact match. Omitting this argument defaults the formula to the TRUE value. In an ordered list, TRUE finds the closest value without going over. TRUE and FALSE can also be entered numerically as a 1 or 0, respectively.

Objective: Look up the column header Net Profit and return value located in the row containing 6/30/2013                                        

  1. What do I want to look up?  I want to look up Net Profit which is located in cell E20.
  2. Where do I want to look it up? I want to find Net Profit in the header of the table located at the range B20:E32.
  3. What row do I want the formula to return?  The 6/30/2013 value is located in row  7 of the table specified above.
  4. Exact match or not? I am looking for an exact match so I’ll set this to FALSE.

Result: the formula returned a value of 2,625. Looking in the data table I can confirm that this is the correct value.               

click here to open the excel spreadsheet shown below: hlookup_example


Leave a Reply