HLOOKUP Function in Excel

 

What is the HLOOKUP function in Excel?

→ The HLOOKUP function works as a horizontal lookup by searching for a value in the first row of a table array and returning us the value available in the same row and defined in the row (index_number position).

→ This function works in a table organized horizontally. H stands for Horizontal in HLOOKUP.

→ It is a built-in function of MS Excel and it is very popular among all.

→ We can put it into a Lookup or Reference Function category.

➨ How to use the HLOOKUP function in Excel?

→ Refer below the article for the HLOOKUP function explained with examples.

We need to enter a simple formula in the MS Excel spreadsheet for getting the value from the table.

 

What is the syntax of the HLOOKUP function?

→ The syntax for the HLOOKUP function in Microsoft Excel is:

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

The Syntax Parameter or Syntax Arguments are:

→ lookup_value – This is the value that should be available in the first row of a table array and also we are searching this value in the 1st row of the table. In other words, what you want to lookup?

→ table_array – The table from which we need to pull out the data. In easy language, we can say that from where you want to lookup?

→ row_index_number - The row in the table from which we need to pull out the data after comparing the lookup value in the 1st row of the table.

→ [range_lookup] – Range_Lookup helps us for either we need to pull out data with an exact match or approximate match. We have two values for that (1) TRUE = approximate match or we can add value as “1” (one), and (2) FALSE = exact match or we can add value as “0” (zero).

Important Points for using the HLOOKUP function

→ We can use the HLOOKUP function with any data type like a string, number, date, etc.

 

→ If we mention "FALSE" or "0" for the approximate_match in range_lookup parameter and there is no exact match found, then the HLOOKUP function will return #N/A.

 

→ If we mention "TRUE" or "1" for the approximate_match in the range_lookup parameter and no exact match is found, then the next smaller value is returned.

 

→ If we specify the value of an index_number is less than 1 then the HLOOKUP function will return #VALUE!.

 

→ If we specify the value of an index_number is greater than the number of rows are available in the table then the HLOOKUP function will return #REF!.

Examples of HLOOKUP Function:

→ Now we are taking on an example of a stock sheet of a supermart.

So in the picture, the stocks and prices of various fruits are mentioned in the table.

→ We need stock and price of specific fruit mentioned in cell no B5. Here we have mentioned the “Orange” in cell B5.

→ So we will apply the HLOOKUP formula for this as we know the syntax of this function as mentioned below.

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

 

→ lookup_value – in this example our lookup value is cell no B2 because we want the price and stock of fruit that is mentioned in cell D2. In this example, we have taken it is an Orange.

→ table_array – Here our table array is A1:F3 – this is the whole table from this table we want to pull out the value of stock and price.

→ row_index_number – For the stock purpose, we will mention the row index number is 2 because in this table the stock is mentioned in the 2nd row. In the same way for the price row, we will mention the 3rd row.

→ [range_lookup] – This is used for the approximate match or exact match, in this example, we want the exact match value from the table with cell B5. So we will mention the “0” or False in the formula.

→ Now, Our formula for the stock of Orange =HLOOKUP(B5,A1:F3,2,0) while we have to write Orange in cell B5. So this formula will give us the stock of orange = 90 kg.

→ Similarly, we can get the price just changing the row index number 3 instead of 2. So the formula for the price of orange =HLOOKUP(B5,A1:F3,3,0). So this formula will give us the price of orange = 7$.

👉 For a regular update:

➨ Join us (Telegram Channel)

➨ Join us (LinkedIn Page)

👉 Also Navigate Our Popular Category:

➨ Statistical Functions

➨ Date & Time Functions

➨ Logical Functions

➨ Lookup & Reference Functions

➨ Math Functions

➨ MS Excel Sitemap

➨ 7 QC Tools Excel Templates

➨ Lean Six Sigma Excel Templates

➨ MS Excel Templates