How to use HLOOKUP Function in MS Excel

HLOOKUP can be used to search for data in a table and return value from any specified cell in same row. The data which is searched for should be in first row of data range. Also data list should be in horizontal direction.

The HLOOKUP function searches in horizontal direction (left to right) in the topmost row of a Lookup table until matching value or value exceeds the one that you’re looking up is found.

Syntax HLOOKUP function

=HLOOKUP (value, table, row_index, [range_lookup])

Where

value is  the value that you want to look up in the table

table is the cell range or name of the lookup table that contains both the value to look up and the value it is required to return.

row_index argument in the VLOOKUP function is the number of the column whose values are compared to value argument in a vertical table.

range_lookup is optional argument and is the logical TRUE or FALSE that specifies whether look for an approximate or an exact match for the value in table. If you provide true for this argument and no match is found , your formula will return the next smallest value. Also if you provide false for this argument and no match is found, your formula will return #N/A.

Errors in HLOOKUP function in excel

  • # VALUE! error – you will get #VALUE! error if row_index argument is less than 1
  • #REF! error – you will get #REF! error if row_index argument value is more than number of rows in table

Example of use HLOOKUP in excel

Here we have a table with details of sale for items with Sale ID, item name and quantity.

Now if we want search name of item from ID and quantity sold we will use HLOOKUP

Formula used to search item sold  from item ID.

=HLOOKUP(J9,B3:H5,2,FALSE)

Lets see how does this formula actually work

value argument is referring to cell J9 which contains item ID

table argument used is B3:H5 which is range of your table

row_index argument is 2 referring to row number 2 of table which has list of items in this rows.

range_lookup argument is false that is we want HLOOKUP function to search for exact match.  So if exact item ID is not found then formula result will be #N/A.

Second formula used to search quantity of item sold from item description in cell J10.

=HLOOKUP(J10,B4:H5,2,FALSE)

Working of this HLOOKUP formula is simple.

value argument is referring to cell J10 which contains item Description

table argument used is B4:H5 which is range of your table

row_index argument is 2 referring to row number 2 of table which has list of items in this rows.

range_lookup argument is false that is we want HLOOKUP function to search for exact match of item description.  So if exact item description is not available then formula result will be #N/A.

To download sample file click here HLOOKUP

Related

  1. How to use VLOOKUP Function in MS Excel
  2. VLOOKUP with two or more criteria