Vlookup and ="" Question

I

IvenBach

Board Regular
Joined
May 31, 2008
Messages
212
I use vlookup frequently and at times when there is no value in the resulting Row and Column vlookup returns 0. I usually wrap vlookup in an IF statement that looks similar to =IF(VLOOKUP(L1,$A$1:$B$15,2,0)=0,"",VLOOKUP(L1,$A$1:$B$15,2,0)).
When I copy as values and then use Ctrl+Arrows to navigate it will consider the cell that had "" pasted in it as a filled cell. The same goes for GoTo Special: Blanks. If I check its length it comes up as 0 and Isblank returns FALSE. I get a #VALUE! error as I expected when trying to grab its Code().

I've gotten used to working this way and thought about asking if someone might know why this is occurring. I thought I'd read somewhere previously why this happens but don't want to go around assuming.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Basically "" is a null string. The cell isn't blank because it contains a null string and the length of a null string is zero. =CODE(A1) where A1 contains "" will return #Value.

Clear as mud? :eeek:
 
Upvote 0
It occurs because "" is NOT blank, it's a Null Text String.
Even after copy/paste special/values.


To resolve, Highlight your effected range, and run this macro..


Sub ClearNullStrings()
Selection.Value = Selection.Value
End Sub


Hope that helps.
 
Upvote 0
VoG - Thanks. Just wanted to make sure I wasn't thinking things up on my own.

jonmo - Ran your macro and worked nicely. Mind explaining whats the difference in computer speak between "" and reassigning the value with itself. Seems like it'd just come up with the same null string.:confused:
I came up with a simple function that I use occasionally. When I put it into the cell itself [=PERSONAL.XLSB!ifZERO(VLOOKUP(L1,$A$1:$B$15,2,0))] it comes up as as #VALUE! as well. I'm still learning and think its because the .Value2 is trying to reference itself... Should I start new thread for this instead and link to it?

Code: 
Function ifZERO(varARGUMENT As Variant)
    If varARGUMENT.Value2 = 0 Then
        ifZERO = ""
    Else
        ifZERO = varARGUMENT.Value2
    End If
End Function
 
Upvote 0
Going back to what you gave me jonmo about the nullstring being replaced with the vba code I noticed that if I have a cell that has the nullstring contained and I use Ctrl+D to copy form the cell above it inputs the nullstring in properly. However, if I use Ctrl+Q (which I believe copies the visible cells contents) and then enter that in it comes up as a True isblank leading me to believe that it is never actually entered :eeek:. Either of you have any knowledge on why this happens? I'm guessing it happens because when you hit enter it's removing the nullstring as opposed to when you copy it down you aren't.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VLOOKUP Isssue
Replies
6
Views
111
Peter_SSs
Peter_SSs
J
  • Locked
  • Question Question
LET/VSTACK/FILTER question
Replies
6
Views
383
Fluff
Fluff
S
  • Question Question
Vlookuping matching values with hidden rounding errors
Replies
5
Views
533
awoohaw
awoohaw
D
  • Question Question
Lookup value based on two column criteria
Replies
3
Views
465
Cubist
Cubist
L
  • Question Question
Another VLOOKUP Question
Replies
4
Views
447
LogPlanner
L

Forum statistics

Threads
1,224,544
Messages
6,179,430
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top