Excel HLOOKUP Examples (Table of Contents)
- HLOOKUP Examples
- HLOOKUP Example in Excel – Illustration #1
- HLOOKUP Example in Excel – Illustration #2
Examples of HLOOKUP
Hlookup is used for mapping and looking up the values that are horizontally filled. Similar to Vlookup, Hlookup is also having the same syntax. Below are the different Hlookup examples in Excel.
HLOOKUP Example in Excel – Illustration #1
Here we have sample data of some customers who bought some products on different dates of a month and the data is horizontal.
Now we will test Hlookup for the data shown in the above table and location of the output cell will be B7 which is marked below with heading Quantity.
Before we apply Hlookup, first let’s see the syntax of Hlookup as shown below.
- Lookup_value: It is the fixed reference lookup cell for which we need to find the value.
- Table_array: It is that table from which we need to find the value.
- Row_index_num: Exact sequence of a row for looking up the value.
- [Range_lookup]: Here we will fix an exact or approximate match as per data type.
Now let’s use Hlookup in excel. For this go to that cell, where we need to find the value. Here we are choosing cell B7.
Type “=” (Equal) sign. By this, all the inbuilt functions in excel will get enabled. Now select Hlookup.
Lookup_Value = Select the reference cell A7.
Table_array = Select table array B1:K3, which covers the complete table.
Row_index_num = Select row 2, by typing 2.
[Range_lookup] = Select FALSE or “0” for an exact match as our data is quite simple to understand.
As we can see the above screenshot, how our applied Hlookup final formula will look like. Red color table is our table array, Blue color cell is our lookup value and 2 is row 2 from the selected table array.
Once we press Enter, we will get applied formula as shown below.
#N/A in the above screenshot can be seen because there is no reference value filled in cell A7. But that doesn’t mean that applied formula is incorrect. The correctness of the formula can be measured once we enter some value from the table array to cell A7. Now fill cell A7 with any of the table_array value of same header name Customer Name. Let’s enter Customer 4 here and cell the result.
As we can see in the above screenshot, after entering Customer 4 in A7, applied Hlookup mapped the value from the selected table array of the reference cell to give the value like 23 in B7. This is how Hlookup works.
HLOOKUP Example in Excel – Illustration #2
There is one more method of applying Hlookup. For this, put the cursor to a cell where we need to apply Hlookup and then go to Formula menu tab and click on Insert Function as shown in below screenshot.
Once we click on it, we will get the Insert Function box which has all the list of inbuilt functions of excel as shown below. From the tab or select a category, choose All as circled below to get the list of all functions and from Select a function box, select Hlookup and click on OK. Or else type Hlookup or Keyword related to this in Search for a function box.
After that, we will function the argument box, when we need to select the same range and criteria as we did in example 1 and click on Ok.
As we can see the Formula result is showing blank. But it doesn’t mean that our formula is incorrect. We will check the correctness of formula once we click on Ok and put the lookup value in A7. After clicking on Ok, we will get the below-applied formula.
Now to test if the formula is applied or not, enter any customer name, in cell A7. For testing, let’s enter Customer 5 in cell A7. Once we do this, the value of Customer 5 from the table array will get looked up with the same header Customer Name as 55.
As we got the looked up value like 55 in cell B7, which means that our applied formula is correct.
Things to Remember
- If the Lookup Value is not the exact value in the data table, we will get a # N/A error.
- The structure of the data table is very important. If the data table is horizontal, HLOOKUP should be applied and VLOOKUP should be applied if the table is vertical.
- Like VLOOKUP, HLOOKUP also limits the collection of data from top to bottom and not from bottom to top.
Recommended Articles
This has been a guide to HLOOKUP Examples in excel. Here we discuss various HLOOKUP Examples in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
