Excel hlookup function with example

Hey friends, HLOOKUP is very useful functions in Excel & I know many of us to have few questions in mind regarding HLOOKUP

What is HLOOKUP in excel?

How do we use HLOOKUP in excel?

What is the difference between VLOOKUP & HLOOKUP?

 

In Excel HLOOKUP function is used to retrieve data from a specific row in the table. The “H” stands for “Horizontal”. The value for which we need to retrieve data from table must appear in the first row of the selected table, with lookup raw down. Let’s see the use of the hlookup function with example.

Purpose: Retrieve a value from the table by matching the first row of a table.


HLOOKUP Formula Syntax

HLOOKUP(lookup_value,table_array,raw_index_num,[range_lookup])
Argument:

Lookup_value: The value to look for in the first row of a table.

Table_array: The table from which to retrieve a value.

Row_index_num: The row in the table from which a value need to retrieve

Range_lookup: It is optional,

TRUE= approximate match

FALSE= exact match

Now we are familiar with excel hlookup formula but in practice, may you will face the questions like “How to use hlookup in excel?” So let’s dive into a tutorial of hlookup in excel with an example.


Use of HLOOKUP with example

hlookup function in excel with example

Explanation:

In the above tutorial, we need to retrieve a value of salesperson from row 3 for sales achieved target & row 4 for a number of accounts. For that, we use HLOOUP in Cell B7 & B8 simultaneous. By using HLOOKUP we can find a value for the name entered in B6. HLOOKUP  is a very useful formula through which we can find specific value from a large table.

#N/A: If HLOOKUP cannot find value from the lookup table, returns #N/A error.

hlookup function in excel with example

Since the value of cell B6=Michel is not present in lookup raw A1: H1, the result of HLOOKUP in cell B7 & B8 is #N/A error.

Let’s convert #N/A error to specified value with the use of IFNA in conjunction with HLOOKUP function.


IFNA in conjunction with HLOOKUP

excel hlookup function tutorial

With the help of IFNA in conjunction with HLOOKUP, we replaced #N/A with specified value” Not use”.  Excel treat this specified value as 0.

Similarly to find value from column we can use VLOOKUP function.

Recommended For You

About the Author: Mehul Vaghela

Howdy Math fan. I'm Mehul Vaghela - B.E.Mechanical. Enthusiastic and holds the passion of reading, writing, and learning. In 10 years of experience on the field, I've faced so much problem with managing data and learned to make that happen. ExcelElixir is one of the parts of passing it on (knowledge).

Leave a Reply

Your email address will not be published. Required fields are marked *