6 Reasons why HLOOKUP not working - Solved

HLOOKUP not working !!!

Don’t need to get panic. 

I know you have tried a lot but all in vain. 

Calm down, there are very few reasons behind not working of HLOOKUP function.

Don’t hurry. 

First understand why HLOOKUP is not working, therefore, we will dig deeper to solve the problem permanently.

In this article, I will discuss with you how to trace the possible errors while implementing HLOOKUP and how to rectify these errors.

But before going I would like to review the SYNTAX of HLOOKUP once again.

It’s very like VLOOKUP, technically only one change is in the syntax and it is ROW_INDEX_NUMBER in place of COL_INDEX_NUMBER in VLOOKUP in the third parameter of HLOOKUP function.

We all know the ‘H’ signifies Horizontal in HLOOKUP, and ‘V’ for Vertical in VLOOKUP.

Lets review the syntax again. For more details, you may read this article.

SYNTAX

=HLOOKUP( lookup_value, table or array, row_index_number, [range_lookup] )

Note :- Please note our brain is trained to lookup vertically, from left to right – generally in daily life we compare or match column wise in VLOOKUP.

On the contrary, in HLOOKUP we match ROW wise, the opposite of VLOOKUP.

Therefore, the most important thing is to change our mind setup from left to right to up and down. This is how HLOOKUP works.

Below is the few major Check List to check why HLOOKUP is not working,.

1.               Not Having Lookup_Value in First Row :

If LOOKUP VALUE (which you are searching for, the first parameter) is not available in the table or array from where you are getting or matching data or source table, you will get #N/A error.

Please watch carefully the below image. Here I want to match the employee with the Sales Quantity in the small table from the large table. Here I am getting #N/A error because “Employee 14” is not present in the first table.

HLOOKUP not working

This is a small example, therefore, what you need to do is search manually for the Lookup Value by pressing Ctrl+F in your Keyboard.

If Value is not found, then it’s OK, use IFERROR to mask the #N/A error.

2.               Counting the Wrong Number of Rows for ‘Row_index_number’ parameter

Please recount the ROW NUMBER from where you select the rows in the first table from where you are fetching data.  Remember this count will be from TOP to BOTTOM unlike in VLOOKUP from left to right.

Note : Counting ROW Number starts from ‘1’ and  include the very first column as shown in the picture.

In this example I wrongly count the column number as 4 instead of 3.

=HLOOKUP(E11,B3:J5,4,0)

The error will be #REF error.

HLOOKUP not working

3.               Extra Space or Characters

Please check for Extra spaces in your formula, if you have doubt that there might be extra space, use TRIM function to remove extra spaces.

To learn more about TRIM,click here.

4.               Not Using ‘FALSE’ for Exact Matching [Range_Lookup]

Although, sometimes HLOOKUP works perfectly but do not forget to use [Range_Lookup], False or 0 for exact matching and True or 1 for approximate matching. It’s always better to make a good habit.

5.               Forgetting Absolute References (pressing F4 can do this) When Copying the Formula

While copy, and paste HLOOKUP formula or just dragging you must put $ sign to restrict the column not to move down or side by side. This is called Absolute reference.

There are two ways to do this, one you insert a $ sign manually or you can use F4 (Function Key) key.

HLOOKUP not working

6.               A new row has been inserted in the old table where HLOOKUP was working

In a previously calculated table where HLOOKUP has been used and now inserting a new row will create HLOOKUP unstable.

To resolve this either you can recheck your HLOOKUP formula or you can use an additional function MATCH to be nested with HLOOKUP. Using MATCH with HLOOKUP will make your formula very dynamic.

Please click here to learn how to useMATCH function with HLOOKUP.

I hope you have enjoyed this article. Take care…

Thank you for reading….

Integer portion of a division – Quotient Function

In this article, I will discuss about Quotient Function in Microsoft Excel to get the integer portion of a division.

As we all know, there are three parts in a division and in a division, the dividend (Numerator) is divided by the divisor (Denominator) to get a quotient.

Parts of a Division

Parts of a Division

Description

The Microsoft Excel QUOTIENT function returns the quotient or the integer part of a division.

Syntax

The syntax for the QUOTIENT function in Microsoft Excel is very simple

= QUOTIENT (numerator, denominator)

Parameters or Arguments

numerator

The dividend.

denominator

The divisor.

Returns

The QUOTIENT function returns the integer part of a division.

Example :

The below image will clarify Quotient function very well. This function is very easy to implement.

How to Use Quotient Function in Excel 

I hope you have enjoyed this small article. Take care…

Thank you for reading….

REPT Function

In this article, I will discuss about a very small function in Microsoft Excel, REPT function. Although REPT function is used very rarely in Worksheets but it is very useful in Excel VBA or Excel Macro.

Most probably REPT is derived from Repeat word, so we can easily understand that this function has the capability to repeat some data.

We will use this function in Excel Macro in our upcoming articles to repeat some specific data.

Description

The Microsoft Excel REPT function returns a repeated text value a specified number of times.

It can print the numbers and special characters also.

Syntax

The syntax for the REPT function in Microsoft Excel is very simple

=REPT( text, number )

Parameters or Arguments

text

The text or number or special character to repeat.

number

The number of times to repeat the text value.

Returns

The REPT function returns a string/text value.

Example :

The below image will clarify this tiny function very well.

How to Use REPT function to repeat texts

I hope you have enjoyed this small article. Take care…

Thank you for reading….