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.
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.
I hope you have
enjoyed this article. Take care…
Thank you for reading….
1 comment:
Nice
Post a Comment