Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

I have a table (aRange) with a blank Cell

eg.

5 r Some Value which could be 0

3 f

4 t Some Value which could be 0

When my vlookup formula <=VLOOKUP(3,aRange,3,FALSE)> refers to said blank cell it returns 0 instead of ""

The values in the column could be 0 so work around

<=IF(VLOOKUP(3,aRange,3,FALSE)<>0,VLOOKUP(3,aRange,3,FALSE),"")>

is not acceptable

Short of writing my own UDF how do I make vlookup behave as I believe it should ie return ""

If theres no answer apart from the UDF has anyone done one already to save me wasting time?

See the example if the above is not clear

Thanks in advance.

vlookup-Issue.xls

eg.

5 r Some Value which could be 0

3 f

4 t Some Value which could be 0

When my vlookup formula <=VLOOKUP(3,aRange,3,FALSE

The values in the column could be 0 so work around

<=IF(VLOOKUP(3,aRange,3,FA

is not acceptable

Short of writing my own UDF how do I make vlookup behave as I believe it should ie return ""

If theres no answer apart from the UDF has anyone done one already to save me wasting time?

See the example if the above is not clear

Thanks in advance.

vlookup-Issue.xls

The simplest solution is to make sure your lookup table doesn't contain empty cells.

Select the cells in the lookup table.

Choose Find & Replace (Ctrl+H)

Don't enter anything in the Find box.

In the Replace box put

=""

This will return "" rather than 0

You could try changing to INDEX/MATCH instead of VLOOKUP. Or get the cell address with VLOOKUP and then use OFFSET. Basically, you'll have to check for the cell itself and not the value that VLOOKUP returns.

<=IF(VLOOKUP(3,aRange,3,FA

is not acceptable

Why is it

You are quite wrong on this, I'm afraid, and Stephen is correct. This is not a VLOOKUP issue, it's just how Excel works. Consider a sheet where A1 is empty:

=A1

will return 0

=A1=""

will return True

and

=if(A1="","",A1)

will return ""

Regards,

Rory

>> When my vlookup formula <=VLOOKUP(3,aRange,3,FALSE

Thus, testing for "" wouldn't work because it returns 0 instead. I'm not saying it happens with all VLOOKUP cases. But in his case, it appears to be so.

=VLOOKUP(3,aRange,3,FALSE)

That will return zero or blank as required....downside is that it converts everything to text format, which might be a problem for onward calculations. Otherwise Stephen's suggestion is the way to go

regards, barry

even viewing the result in th eformula editor or in vba vis worksheetfunction.vlookup ... etc it gives the result of 0

so in this case <cellwithresult>.value = <cellwithresult>.text = 0 when the vlookup looks up ""

so its not just a case of the sheet displaying "" as 0 -apparently ""=0 in actual fact

I'm really happy that stephens workaround works as well

(I would call that a bug also)

Who would have thought 2 bugs = a correct solution

I'm so confused

Automated Request for Attention: Q_26860835Question:

Please attend to the following question:

http://www.experts-exchange.com/Programming/Misc/Q_26860835.html

My question is in 2 Zone(s).

My question has 13 Total comment(s).

My reason is: I awarded the 450 points to the wrong solution

It should have been the next one down (stephens)

Sorry

Can a mod please fix this or undo the points & I will redo

Cheers

=IF(ISBLANK(VLOOKUP("help"

tested on simple range of two cells A3:B3 with "help" in A3. Changing contents of B3 from blank or non-blank affects the result.

To allow for errors:

=IF(ISERROR(VLOOKUP("help"

Thanks

Rob H

Thanks

Rob H

regards, barry

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

=IF(VLOOKUP(3,aRange,3,FAL