Excel's HLOOKUP Function

01
of 04

Find Specific Data with Excel's HLOOKUP Function

Using the Excel HLOOKUP Function
Using the Excel HLOOKUP Function. © Ted French

Using the Excel HLOOKUP Function

Excel's HLOOKUP function, short for horizontal lookup, is used to find specific information that has been stored in a spreadsheet table.

HLOOKUP works much the same the Excel VLOOKUP function, or Vertical Lookup.

The only difference being that VLOOKUP searches for data in columns and HLOOKUP searches for data in rows.

If you have an inventory list of parts or a large membership contact list, HLOOKUP can help you find data that matches specific criteria such as the price of a specific item or a person's phone number.

02
of 04

Excel HLOOKUP Example

Using the Excel HLOOKUP Function
Using the Excel HLOOKUP Function. © Ted French

Excel HLOOKUP Example

Note: Refer to the image above for more information on this example. The syntax of the VLOOKUP function is covered in detail on the next page.

=HLOOKUP("Widget",$D$3:$G$4,2,False)
  • "Widget" - this HLOOKUP function is looking for the price of Widgets.
  • $D$3:$G$4 - it is looking for this information in the data table located in cells D3 to G4.
  • 2- HLOOKUP is looking for the price in the second row of the table.
  • False - indicates that only an exact match to the lookup _value "Widget" will be accepted.

The HLOOKUP function returns the results of its search - $14.76 - in cell D1.

03
of 04

HLOOKUP Function Syntax

Using the Excel HLOOKUP Function
Using the Excel HLOOKUP Function. © Ted French

Excel HLOOKUP Function Syntax:

=HLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

lookup _value:
This argument is the value that is searched for in the first row of the table array. The lookup _value can be a text string, a logical value (TRUE or FALSE only), a number or a cell reference to a value.

table_array:
This is the range of data that the function searches to find your information. The table_array must contain at least two rows of data. The first row contains the lookup_values.

This argument is either a named range or a reference to a range of cells.

If you are using a reference to a range a cells, it is a good idea to use an absolute cell reference for the table_array.

If you do not use an absolute reference and you copy the HLOOKUP function to other cells, there is a good chance you will get error messages in the cells the function is copied to.

row_index_num:
For this argument, enter the row number of the table_array from which you want data returned from. For example:

  • if the row_index_num is 1, it returns a value from the first row of the table_array;
  • if the row_index_num is 2, it returns a value from the second row of the table_array.

range_lookup:
A logical value (TRUE or FALSE only) that indicates whether you want HLOOKUP to find an exact or an approximate match to the lookup_value.

  • If TRUE or if this argument is omitted, HLOOKUP will use an approximate match if it cannot find an exact match to the lookup_value. If an exact match is not found, HLOOKUP uses the next largest lookup_value.
  • If FALSE, HLOOKUP will only use an exact match to the lookup_value. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, an #N/A error is returned.
04
of 04

HLOOKUP Error Messages

Excel HLOOKUP Error Value
Excel HLOOKUP Error Value. © Ted French

Excel HLOOKUP Error Messages

  • If the lookup_value is not found in the first row of the table_array, HLOOKUP returns the #N/A error value.
  • If the row_index_num argument is greater than the number of rows in table_array, HLOOKUP returns the #REF! error value.
  • If the table_array argument includes empty rows at the top of the table_array, HLOOKUP returns the #N/A error value.