HLOOKUP in Excel

ADVERTISEMENT

---

HLOOKUP Function in Excel is a kin of VLOOKUP Function . The H in the HLOOKUP remains for "Horizontal" and thus it is regularly called as Horizontal Lookup.
HLOOKUP is useful function for creating horizontal lookups.
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)

Here, ‘lookup_value’ refers to a value that is to be searched in the topmost row of the table. ‘lookup_value’ can be a value, a reference or a text string.

‘table_array’ is the range reference or range name of an array of values, inside which in which the data is to be looked up.

‘row_index_num’ is the row number in the ‘table_array’, from which the matching value is to be returned. A ‘row_index_num’ equal to 1 returns a value from the topmost row in the ‘table_array’ and similarly a ‘row_index_num’ equal to 2 returns a value from the second row of the ‘table_array’.


‘range_lookup’ argument accepts a Boolean value that specifies whether you want Horizontal Lookup function to return an exact match or an approximate match

To perform HLOOKUP here consider one example
In following table we have to find the marks of Hari in English to find marks here I used the HLOOKUP function






Here "Hari" which is the value to find out in table,
"B2:F5" is the table to find out Hari and value,
"4" is the row index "count from top to english start count from 1"

1. Horizontal Lookup performs a case insensitive lookup. This means, it treats “Hari” and “hari” as same.
2. While using HLOOKUP function ‘lookup_value’ should always be in the topmost row of the ‘table_array’.
3. ‘range_lookup’ is an optional argument. If it is omitted then HLOOKUP takes its default value as TRUE (approximate match).
4. If HLOOKUP cannot find the ‘lookup_value’, and ‘range_lookup’ is TRUE (approximate match), it uses the largest value that is less than ‘lookup_value’.
5. Similar to VLOOKUP, HLOOKUP also supports wildcard characters (like: ‘*’, ‘?’) in the ‘lookup_value’ argument (only if ‘lookup_value’ is text).
6. If ‘range_lookup’ is FALSE and HLOOKUP is unable to find the ‘lookup_value’ in the defined range, then it returns a #N/A error.
7. If the ‘row_index_num’ is less than 1, HLOOKUP returns #VALUE! error. If it is greater than the number of columns in ‘table_array’, then it returns #REF! error.