How to use HLOOKUP function in MS-EXCEL (beginner)

In MS-Excel HLOOKUP is a built-in function which is categorized as a Lookup/Reference Function where ‘H’ stands for Horizontal. According to techonthenet, HLOOKUP function performs a horizontal lookup by searching for a value in the top row of the table and returning the values in the same column based on the index number.

HLOOKUP can be used as a worksheet function (WS) in Excel. As a worksheet function, the HLOOKUP function can be entered as part of a formula in a cell of a worksheet.

Anyway, HLOOKUP function can be used for a large set of data in a single or multiple tables to find or lookup a specific value in a faster way.

But in this demonstration, we will use a single table contains a small amount of data and find out some data through HLOOKUP function.

Basic Syntax

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

This function contains some arguments and each has a specific meaning. So, let’s take a closer look at what these arguments actually pass in the function.

lookup_value: This is the value you are looking for in the first row of the table.

table_array: Two or more rows of data which are sorted in ascending order or the range you would like to define from the table to lookup the value.

row_index_number: The row number in the table from which the matching value can be retrieved. For first row you can write 1 and for other row just write in a numeric value of that row.

range_lookup: this argument contains logical value. If, True that means the approximate match of that value and if, False which means it will look for the exact match of the value from the table. It is an optional field and by default, it is True in nature.

Well, working with example is the easiest way to understand how HLOOKUP function work. 

Now, take a closer look at the below screenshots and data values passed here for better understanding.  

Here is a demo table with some demo data to perform HLOOKUP function.

Then, we will chose a blank cell where we can look up our required value through HLOOKUP function. We will use K2 cell for the operation.

Suppose, we have a scenario that there is a table of some products for an online grocery store and customers ordered these products in the above manner. Each order has a unique OrderID with the UnitRate of the product and the number of unit (Quantity) they want.

Let’s think, we need to look up the UnitRate for a specific OrderID, e.g. we need to look up the UnitRate of OrderID=19881. From the table we can easily say that, it is $9.75 but how can we know using a HLOOKUP function. Let’s have a look on the procedure,

Chose a blank cell, for our case we chose K2,

Then, we can start typing ‘=HLOOKUP and press tab of the keyboard in that cell and manually input the arguments according to our needs. Like the following one, 

But, for the beginners, there’s a second option to pass arguments in HLOOKUP function. Go to the Formulas tab and look for Lookup & reference menu, click on it a Drop Down list will show you a screen like this,

Then just click on it and a new box will pop up. Like this one,

Based on the field, click and type in the box. Like for Lookup_value we want to look for the OrderID = 19881 which is in cell E1, so we will click the cell.

For Table_array, we will select the entire table by dragging the cell from A1 to G3. 

For Row_index_number, we will select the row as the index to view the lookup table. 

We chose 2 because we want to view the UnitRate of the Lookup_value E1.

And, finally, there is another field named Range_lookup and here we wrote FALSE because we want an exact match. 

Then, just click OK and get the result of your HLOOKUP function.

Well, this tutorial is written for very beginner level audiences so we use a very tiny amount of data to demonstrate it and use very simple words to instruct them. 

If you want an advance level of tutorial on How to use HLOOKUP function in MS-EXEL for advance user please do some comments for it and we will provide you a fully functional tutorial with a wide range of data on this topic.

That’s all for today. We will post a new tutorial ASAP! Till then be happy and keep learning. Stay with  tutorialabc & for any inquiry please contact us through our Contact page.