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
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
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.
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
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.