Hi, what does your formula return when there is a valid lookup - is it always a number, always text or can it be a combination of both?
Alright, I have done a search and did not find for 0, for #N/A yes, but not 0.
Here is the formula, very straight forward: =IFERROR(VLOOKUP(A:A,Physician!A:P,5,FALSE),"")
In the results I am getting 0 instead of a "blank".
Any suggestions?
-T
Hi, what does your formula return when there is a valid lookup - is it always a number, always text or can it be a combination of both?
[code]your code[/code]
It can be a combination of number, text or both.
I have used this in the past with no issues, so I thought it was a formatting thing, but that is not the case, so I am kinda stuck, I need the field to be blank if there is no value, and it returns 0. Can't have 0 in a report, lol
The problem is that 0 is NOT an error, it's a perfectly valid numeric value.
Therefor IFERROR does not return "".
Now there are 2 possible reasons for VLOOKUP to return 0.
1) The cell corresponding to where the matching value was found is Actually a 0 (0 is a real numeric value)
2) The cell corresponding to where the matching value was found is Blank/Empty
How to resolve depends on which (or both) scenario is the cause of your 0 from Vlookup.
Last edited by Jonmo1; Nov 7th, 2017 at 11:50 AM.
Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
Find a link in post number 31
The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Ferris Bueller A.K.A. John Hughes, 1986
You can suppress the zero from displaying by using a custom format along the lines of.
General;-General;
Or you can remove it totally with something like this:
=IFERROR(IF(VLOOKUP(A1,Physician!A:P,5,FALSE)=0,"",VLOOKUP(A1,Physician!A:P,5,FALSE)),"")
Note that the lookup_value should refer to a single cell - not the whole column; change this to the cell that contains your look up value.
[code]your code[/code]
There is an issue with your VLOOKUP formula.
The first argument (the value you are looking up/matching on), should be a single cell, NOT a whole column!
So, it should look something like:Code:=IFERROR(VLOOKUP(A:A,Physician!A:P,5,FALSE),"")
Code:=IFERROR(VLOOKUP(A1,Physician!A:P,5,FALSE),"")
TIPS FOR FINDING EXCEL SOLUTIONS
1. Use the built-in Help that comes with Excel/Access
2. Use the Search functionality on this board
3. A lot of VBA code can be acquired by using the Macro Recorder.
"Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"
The basic issue is that a formula cannot return 'Nothing' or 'Blank'.
All formulas MUST return 'Something'
So if the result of a formula is a reference to an empty cell, it returns it as a 0.
So my question here is do you need to differentiate between a cell with an actual 0, and a blank/empty cell ?
with this formula
VLOOKUP(A:A,Physician!A:P,5,FALSE)
In column E, the resulting value of the vlookup.
Is a 0 an actual real possible result?
Last edited by Jonmo1; Nov 7th, 2017 at 12:07 PM.
Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
Find a link in post number 31
The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Ferris Bueller A.K.A. John Hughes, 1986
Based on how you wrote your formula, it might not actually be looking up the value you think it is.It is not a 0 it is an actual result (i.e.) Mr. Brown, however, if there is no value it is blank.
You should really adjust your formula like FormR and I described, so you tell it exactly which cell you are looking up (instead of using a complete column reference in your first argument).
TIPS FOR FINDING EXCEL SOLUTIONS
1. Use the built-in Help that comes with Excel/Access
2. Use the Search functionality on this board
3. A lot of VBA code can be acquired by using the Macro Recorder.
"Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"
Like this thread? Share it with others