HLOOKUP stands for Horizontal Lookup and can be used to retrieve information from a table by searching a row for the matching data and outputting from the corresponding column. While VLOOKUP searches for the value in a column, HLOOKUP searches for the value in a row.

To perform HLOOKUP, prepared a data as given below with product, Product Id & their Prices. We need to lookup for the Prices for different product:

 

In the picture above, we need to look for the prices of watches, so the formula will be:

=HLOOKUP(Lookup_Value i.e. A7

Next step is to select Table followed by a comma from which data need to be rectified i.e.

=HLOOKUP(A7,Table_array i.e A1:G3

In Picture above, Table_Array is mentioned as 1:3 as I have selected entire row from 1 to 3.

Next Step is to select, Row_Index_Num i.e. selecting the number of Row where the price lies, here price lies in row no 3. So, we will select index number as 3.

Last step, is to select True(1) or False (0), it signifies that the value you want should be exact match or approx. match. If you want to get approximate match then select True) & if you want exact Match then you can select False. (Mostly False (0) is selected as we need exact value).

Here We will select False Value:

You will get your Answer:

As you will change the Product Name, Automatically the value will change, as you can see in video below:

Double HLOOKUP

One of the difficult tasks to perform & one of the important tools of Excel.We have 2 different types of data in 2 separate sheets. We need to collaborate this data into a new sheet (Sheet 3). One sheet contains Employee name & Product Name & second sheet Contains Product Name details with their Respective Brands. I want a separate sheet, where in front of Employee Name Brands of laptop to be mentioned. So, we used double HLOOKUP Formula.

Next step is to Apply Double HLOOKUP Formula, so first we will apply formula of Single HLOOKUP,

Lookup_value here is Name i.e. Rohit

Table_array is Sheet no 1 (Select row 1 & 2)

row_index_num is 2, as need to look up Product name first

range lookup is False or 0, as you can see below in Image:

The Result will be product name (as shown in pic below), but we need Brand name.

Now we will apply second HLOOKUP on same cell, here with the help of sheet 1, we have gathered Product name, & with second HLOOKUP function with gathered product name we will relate sheet 1 so that with Product name it will connect to brand name.

Here, we will apply HLOOKUP before the formula of First HLOOKUP, as shown in pic below:

Next step is to connect it to sheet 2, as shown in picture below:

Here in Double HLOOKUP, Lookup_Value is First HLOOKUP Formula,

Table_array is sheet 2 (Select row 1 & 2)

Row_index_num is 2, as we need brand name

Range_lookup is False or 0.

Here, you related both sheets to get desired Result shown below:

Now you can copy paste the formula in rest of the blank cells:

This is how you can apply double HLOOKUP easily.

FOR BLOGGER BLOG – http://pnautiyal.blogspot.com
For Advance Excel – https://cmaindiagroup.com/pivot-table-in-excel/