How to use Excel HLOOKUP Function

how to use excel hlookup function

Quick Intro

HLOOKUP is VLOOKUP‘s sibling.

You can use HLOOKUP to lookup a value from a top row of the data table, and if that value found, you can retrieve a value which is a number of cells below in the same column. In short, for horizontal lookup.

The alphabet H in HLOOKUP means horizontal, and V in VLOOKUP is vertical. In the below table, we have months name in the first row and product name in the first column.

DIfference Between VLOOKUP Function Excel HLOOKUP Function

Let’s say if you want to lookup for “Jun” month from the first row and want quantity value of “Product-5” then you can use HLOOKUP for that.

And, another condition is, If you want to lookup for “Product 4” and want quantity value of “Apr” month then you can use VLOOKUP for that.

Syntax

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

  • lookup_value A value you want to lookup.
  • table_array A data table or an array from which you want to lookup value.
  • row_index_num A numeric value represent a number of rows below from the top row from which you want the value. For example, If you specify 2 and your lookup value is in A10 in the data table, it will return value from cell B10.
  • [range_lookup] A logical value to specify the type of lookup. If you want to perform an exact match search use FALSE and if you want to perform non-exact match use TRUE (Default).

More Information on Excel HLOOKUP Function

  • You can use wildcard characters with HLOOKUP.
  • You can perform an exact match or an approximate match.
  • While performing an approximate match make sure to sort data in ascending order from left to right. And, if data is not in ascending order then it would return an inaccurate result.
  • If range_lookup is true or omitted, it will perform a non-exact match but return an exact match if lookup value exists in lookup range.
  • If range_lookup is true or omitted, and lookup value is not in the lookup range, it will return the nearest value which is less than lookup value.
  • If range_lookup is false, then there is no need to sort data range.

Examples

In the below example, I have used HLOOKUP Function with match function to create a dynamic formula. And, I have used a drop-down list to change the lookup value from the cell.

How To Use Excel HLOOKUP Function With Match Function

I have used below formula to perform a dynamic HLOOKUP formula.

Using-Excel HLOOKUP Function With MATCH Function

How does this work?

I have used zone name from cell C7 as a lookup value. Range B1: F5 as table array. And, for row_index_num I have used match function to get the row number.

Whenever I change the value in cell C9, it will return the row number from table array. You don’t have to change your formula again and again. Just change values with the drop-down list and you will get value for that.

Sample File

download sample file to learn more about this tips

What’s Next?

To learn more about Excel HLOOKUP Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.

There are also you have VLOOKUP  function which is highly useful. Apart from this, I have a list of excel functions and some real life formulas examples.