Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print
Share on twitter
Share on linkedin
Share on facebook
Share on pinterest
Share on email
Share on print

HLOOKUP

Lookup a value in the top row of a table or an array in Excel
Searches for a value in the top row of a table and then returns a value in the same column from a row you specify in the table

Rational

Lookup a value in the top row of a table or an array.

Description

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.

Syntax

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Parameters

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

Return Value

The corresponding value of a given row number

Key Notes

  • 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.

 

Keep Reading Similar Functions:

Returns the relative cell position of value

MATCH

The MATCH function finds row or column of a given value containing in a data set, cell B3 in a list contains ‘Feb’ =MATCH(B3,B1:B10,0) returns 2 (3rd row).

Read More »
Lookup a value in a left column of a table or an array in Excel

VLOOKUP

The VLOOKUP function extract data in a vertical pattern which means heading in 1st column and value begins from the 2nd column and retrieve data from left to right.

Read More »
Returns a specific position from a list in Excel

CHOOSE

The CHOOSE function returns specific information from a list of data points, =CHOOSE(4,”Railways”,”Roads”,”Airways”,”Waterways”) returns Waterways.

Read More »
Return address of a cell in Excel

ADDRESS

The ADDRESS function returns cell address from given row and column numbers of a worksheet. For example, if you pass ADDRESS(5,6) the function returns $F$5.

Read More »
Lookup a row or column of a table in Excel

LOOKUP

The LOOKUP function search in single row or column for a given value and return value from the same position or, next row or column.

Read More »