sir plus
asked on
BUG with vlookup???
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,FA LSE)<>0,VL OOKUP(3,aR ange,3,FAL SE),"")>
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
Cluskitt - care to test that?
The values in the column could be 0 so work around
<=IF(VLOOKUP(3,aRange,3,FA LSE)<>0,VL OOKUP(3,aR ange,3,FAL SE),"")>
is not acceptable
Why is it not acceptable?
<=IF(VLOOKUP(3,aRange,3,FA
is not acceptable
Why is it not acceptable?
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
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
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.
>> 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.
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.
Actually, not quite right, it is Excel's equation of 0 and "" which seems to cause this.
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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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! :)
ASKER
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
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
ASKER
Title:
Automated Request for Attention: Q_26860835Question:
Please attend to the following question:
https://www.experts-exchange.com/questions/26860835/BUG-with-vlookup.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
Automated Request for Attention: Q_26860835Question:
Please attend to the following question:
https://www.experts-exchange.com/questions/26860835/BUG-with-vlookup.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
ASKER
Stephen
Please object to the points
Please object to the points
sirplus - I may be wrong, but I think you should be able to change the points. Thanks for your consideration.
Thanks for the clarification VM.
Maybe too late to comment but have you tried using the ISBLANK function:
=IF(ISBLANK(VLOOKUP("help" ,A3:B3,2,F ALSE)),"bl ank",VLOOK UP("help", A3:B3,2,FA LSE))
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,F ALSE)),"er ror",IF(IS BLANK(VLOO KUP("help" ,A3:B3,2,F ALSE)),"bl ank",VLOOK UP("help", A3:B3,2,FA LSE)))
Thanks
Rob H
=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
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
Thanks
Rob H
But so does Stephen's and it was posted first, therefore should be accepted as the asker intended, IMO.
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
regards, barry
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
Count me out!
Cheers
Rob H
ASKER
Sorry for the delay
Points are awarded how I intended
Points are awarded how I intended
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