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