Hlookup function returns 0 when there is no value!

**telnoman** · 05-27-2010, 12:52 PM

Hello community!

I have a lot of hlookup functions that are linking to another sheet.
the problem is when there is nothing entered in the cell, it returns a 0 value instead of just blank.

I need to distinguish between a real 0 and the virtual 0 that is returned when the cell is empty, therefore i can NOT use something like:

Please Login or Register  to view this content.

help?

**JBeaucaire** · 05-27-2010, 12:59 PM

I use the LEN() function to test the length of the result to determine if it's a blank cell or a cell with a zero in it.

If my original formala were:

=VLOOKUP("Joe", A1:E11, 5, 0)

...and that gave me zeros for both empty cells AND for cells with actual zeros in them, I would change it to:

=IF(LEN(VLOOKUP("Joe", A1:E11, 5, 0))=0, "", VLOOKUP("Joe", A1:E11, 5, 0))

**telnoman** · 05-27-2010, 01:30 PM

good idea!
that does work, but would be very tedious in my case, because there are several hlookups that refer to each other.

For example Workbook 1 has a value "" (blank), workbook 2 uses hlookup to get value "" (in form of zero), and workbook 3 uses hlookup to get value "0" from workbook 2!
so I would have to use the LEN() function in both workbooks!

I think your solution is the only one though, so thanks a million for it!

**JBeaucaire** · 05-27-2010, 01:32 PM

It should only be tedious the first time, then it's done...

=======
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

**telnoman** · 05-27-2010, 04:29 PM

I found a stupid solution...
I just put a (') apostrophe in any cell I want to have blank!
and the hlookup functions work fine with that, they return a blank cell

**dave_gordon** · 10-24-2012, 01:32 AM

Brilliant solution telnonman, and it works better with Conditional Formatting
Many thanks

**XtremeMaC** · 03-04-2019, 04:46 PM

Extremely old topic, sorry for the response.
For my file, I wanted to highlight cell values above 15 to be red color. However, all cells have hlookup therefore even the cells with hlookup returning nothing got highlighted red.
Following the idea on this thread, I placed the len(D5)=0 to highlight "blank" cells with no color. This resolved my problem. I prefer this method rather than putting (') as that messes other things up in my file.
Wanted to share for those who search for similar solutions..

**AliGW** · 03-04-2019, 04:56 PM

Welcome to the forum!

Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**Winon** · 03-04-2019, 05:15 PM

@ AliGW,

The OP was not looking for a solution, but merely shared an alternative solution to an old thread.

I honestly feel that this Post of the OP in this old thread, bears valuable information.

Granted. The OP could also have started a new Thread and provided a link to this thread, but would that not be cumbersome?