Solved

BUG with vlookup???

Posted on 2011-03-03
28
415 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:sirplus
  • 7
  • 4
  • 3
  • +5
28 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 35026641
It's up to you whether you regard it as a bug. But it's the way Excel has always worked.

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
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 450 total points
ID: 35026652
What about this?

=IF(VLOOKUP(3,aRange,3,FALSE)="","",VLOOKUP(3,aRange,3,FALSE))
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35026744
StephenJR, if VLOOKUP(3,aRange,3,FALSE) ever returned "", then this thread would be moot. Your suggestion wouldn't work because it will always return false, even for empty cells.

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.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 24

Expert Comment

by:StephenJR
ID: 35026787
Cluskitt - care to test that?
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35026792
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

Why is it not acceptable?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35026802
Cluskitt,
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
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35026868
This is what the OP said:
>> When my vlookup formula <=VLOOKUP(3,aRange,3,FALSE)> refers to said blank cell it returns 0 instead of ""

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.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35026893
So clearly it is not a case of what the VLOOKUP returns, but rather what is displayed. If it were the former then your initial asserton would have been correct.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35026903
Actually, not quite right, it is Excel's equation of 0 and "" which seems to cause this.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35026925
A final note, if you go through the Formula Auditing and lookup both 5 and 3, both vlookups are returned as 0, but the first lookup does not equal "" while the second does!
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 50 total points
ID: 35027089
Another option is to use

=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
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35027109
I haven't tried with the file he posted. I was just running on what the OP said. If it works, that's all that matters. Good job, StephenJR! :)
0
 
LVL 5

Author Comment

by:sirplus
ID: 35041674
PS
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
0
 
LVL 5

Author Comment

by:sirplus
ID: 35045022
Title:
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

0
 
LVL 5

Author Comment

by:sirplus
ID: 35045025
Stephen
Please object to the points
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35055296
sirplus - I may be wrong, but I think you should be able to change the points. Thanks for your consideration.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35055560
Thanks for the clarification VM.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 35142392
Maybe too late to comment but have you tried using the ISBLANK function:

=IF(ISBLANK(VLOOKUP("help",A3:B3,2,FALSE)),"blank",VLOOKUP("help",A3:B3,2,FALSE))

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",A3:B3,2,FALSE)),"error",IF(ISBLANK(VLOOKUP("help",A3:B3,2,FALSE)),"blank",VLOOKUP("help",A3:B3,2,FALSE)))

Thanks
Rob H
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 35341269
I know I came in late after the question had been reopened but I feel that my suggestion actually answers the problem without any sort of  workaround.

Thanks
Rob H
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35341801
But so does Stephen's and it was posted first, therefore should be accepted as the asker intended, IMO.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35345135
The asker originally awarded 450 points to the wrong solution and 50 to me - IMO the thread was only re-opened so that he could re-assign the 450 to StephenJR so I suggest that the points should be split in that way as the Asker intended, 450 to StephenJr and 50 to me (barryhoudini).

regards, barry
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 35346270
Apologies, having read through the thread I had assumed that Stephen's solution hadn't worked so had posted my alternative. I just tried it Stephen's and it does work, surprise!

Count me out!

Cheers
Rob H
0
 
LVL 5

Author Comment

by:sirplus
ID: 35413391
Sorry for the delay
Points are awarded how I intended
0

Featured Post

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question