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
