Lookup a value in the top row of a table or an array.
The Excel HLOOKUP function extract data from a given row of a table or an array of values. So, if a given data is laid in a horizontal pattern which means heading in 1st row and value begins from 2nd row, and you need to look up and retrieve data from left to right, in such cases use HLOOKUP. The HLOOKUP function is a ‘horizontal (H)’ lookup.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
lookup_value – The value to lookup in the top row of the table
table_array – The table to extract data
row_index_num – The row number to return the corresponding value.[range_lookup] – A Boolean argument. TRUE (Default) – to lookup a partial match and FALSE for an exact match
The corresponding value of a given row number
- The HLOOKUP function returns the value by going right of the given row (horizontal) and returns the value in the formula cell.
- The HLOOKUP function works just like VLOOKUP function except that it looks up across a specified number of columns (vertically).
- In case [range_lookup] argument is TRUE (partial match) the function matches the nearest value, which may not be the expected result. It is advisable to select FALSE if the user requires an exact match of given criteria.
- The first row of a lookup table (table_array) needs to sort in ascending order if [range_lookup] is TRUE. Otherwise, HLOOKUP may return an incorrect value.
- If the lookup_value argument is text (and [range_lookup] is FALSE), you can use wildcard characters asterisk (*) and question mark (?).
- A question mark matches a single character, and an asterisk matches multiple characters. A criteria a*a will match with all cells containing a string beginning with “a” and ending in “a” like a text “abide” or “abate”.
- The function returns #N/A error value if HLOOKUP cannot find any data as per arguments (Exact or Partial).
- The HLOOKUP function is not case sensitive.
Example (Image above)
When lookup value in row and return value of another column like here as table is horizontal form and user want cost of sales in quarter 2nd for this we use formula
=HLOOKUP($B12,$B$5:$F$7,3,FALSE) it return 210.