Info, Tutorials, Downloads, Audio Books, Videos, Ebooks and More

Friday, September 23, 2016

HLOOKUP in Excel

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"


Some Important Note about HLOOKUP in Excel
  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.




Check These Also


2 comments:

Dear Readers,

Great !! You've decided to leave comment for us. Let's make meaningful conversation by adding your name.

Please note that,

Comments deemed to be spam or questionable spam, including profanity and containing language or concepts that could be deemed offensive will be deleted.
Including a link to relevant content is permitted, but comments should be relevant to the post topic.