HLOOKUP and its Application

HLOOKUP and its Application

HLOOKUP function performed it search horizontally by matching lookup value located at the first row (unlike VLOOKUP that is vertical) at the top to the row that holds the value you intend to return.

It searched for either EXACT MATCH of the value you intend looking up in the table or closest (Approximately match) value you wish to return.

The syntax is:

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

  • lookup_value: the value being lookup to
  • table_array: table from which to retrieve the data
  • row_index_num: the row number from which to retrieve data
  • range_lookup: A Boolean to indicate exact match or approximate match.

 

Looking for Exact MATCH using HLOOKUP

In the table below, i want to find the salary of each manager in row 5 using their Staff ID as the lookup value. Interestingly the salary information in row 5 in a table is to the bottom of Staff ID (supplied value) at row 1, and this where we allow the almighty HLOOKUP to strut its stuff.

To get this done and return the salary of the manager with “Staff ID” supplied in cell A2, the following HLOOKUP formula needs to be insert into cell B2.

=HLOOKUP (A3, F2:L6, 5, FALSE)

That is Cell B2 = HLOOKUP (search the ‘Staff ID’ appear in cell A2, in the table array (F2:L6), upon sited it (lookup value) bring back the corresponding value in row number 5, and it must be Exact Match (FALSE)

You will get below result

Looking for Approximately Match using HLOOKUP

The optional logical TRUE of the range_lookup argument influence the change here by allow excel to guess approximate match to the lookup value.

HLOOKUP (search this value, in this given table, bring back the value in row number Y of the table as result, [allow Excel to look for approximate match in the absent of exact match])

Let’s see if we can search commission base on each total sale (column F) carried out by sales Representative in ABX Motorcycle Company on different motorcycle brand in the table below.

We will allow Excel to match the closest value in the table array (I1:P3) to the lookup value pick from column F if there is no exact match and THEN return the corresponding commission rate to column G from row 3. The optional logical TRUE of the range_lookup argument make this possible.

The formula is cell G3 will become

=HLOOKUP (F3, I1:P3, 3, TRUE)

In other word, the TRUE can be left out since Excel recognises it as default. That mean the formula can be in this form:  =HLOOKUP (F3, I1:P3, 3)

Hope this post is helpful?

For more Insight on Microsoft Excel, kindly buy any of my books “Productivity Boosting Aspects of Microsoft Excel” and “PivotTable Recipe” on Kindle Amazon. You can as well subscribe to our newsletter here

Shares 0