course bg
EduPristine>Blog>Using HLOOKUP in Excel

Using HLOOKUP in Excel

February 23, 2015

While working on Excel we usually make a lot of vertical tables and therefore end up using VLOOKUP. But there are times when we do have horizontal tables as well, so today we shall learn the function of HLOOKUP.

HLOOKUP is generally used when your data is in horizontal format. HLOOKUP searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array.

How to use HLOOKUP Function?

The HLOOKUP() function has the following syntax:

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

Follow these simple steps for learning the function of HLOOKUP

1. In our example, we want to know the location of all the employees in row 11 as well, so we shall use the HLOOKUP formula to know the same.

Using HLOOKUP function in Excel

2. The first thing that you need to input in the formula of HLOOKUP is lookup_value. It is generally the data that you want to look up. It can be a value or a cell reference; in our case it will be cell reference that is B10.

Filling lookup_value in HLOOKUP

3. The next thing that you need to input in the formula is table_array. Table_array is the range of cells from which we require our data. In our case it is B1:G2. While inputting the table array you should see to it that your cells are locked otherwise when you drag your fill handle to fill the data in other rows it may show N/A (not applicable).

What is table_array in HLOOKUP formula?

4. Next thing in the formula is row_index_num. It is the row number where our value is situated. In our case we have selected two rows that is 1 and 2. So Row 1 remains 1 and Row 2 remains 2. Since the locations are mentioned in Row 2, we shall input 2 as row_index_num.

what is row_index_num in HLOOKUP formula?

5. The next in the formula is range_lookup. This determines that whether you are looking for an exact match (False) or an approximate one (true). This is optional and in our case we shall use False as we are searching for an exact value.

Using range_lookup in HLOOKUP formula

6. When you press enter you will see that you have received the desired result.

How to use HLOOKUP function in Excel

7. To fill the data in other rows as well just drag the fill handle towards your right and your data shall be filled. Make sure that your table array cells are locked (see step 3) otherwise this function may not work.

Using fill handle in HLOOKUP

If you think that this article has helped you in some way or the other, then don’t forget to share it with your friends and if you have any suggestions or queries then you can let us know in the comments box below.

About Author

avatar EduPristine

Trusted by Fortune 500 Companies and 10,000 Students from 40+ countries across the globe, it is one of the leading International Training providers for Finance Certifications like FRM®, CFA®, PRM®, Business Analytics, HR Analytics, Financial Modeling, and Operational Risk Modeling. EduPristine has conducted more than 500,000 man-hours of quality training in finance.

Comments

Interested in this topic?

Our counsellors will get in touch with you with more information about this topic.

* Mandatory Field

`````````````````````````````````````````````````` Post ID = 71649