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

[adsenseyu1]

### 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**