VLOOKUP Results - removing 0

[CypressHCC]

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

 

[FormR]

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?

 

[CypressHCC]

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?

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

 

[Jonmo1]

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.

 

[FormR]

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.

 

[Joe4]

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!

[COLOR=#333333]=IFERROR(VLOOKUP([/COLOR][COLOR=#ff0000]A:A[/COLOR][COLOR=#333333],Physician!A:P,5,FALSE),"")[/COLOR]

So, it should look something like:

[COLOR=#333333]=IFERROR(VLOOKUP([/COLOR][COLOR=#FF0000]A1[/COLOR][COLOR=#333333],Physician!A:P,5,FALSE),"")[/COLOR]

 

[CypressHCC]

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.

Understood, and the value that is in the cell is Blank/Empty, guess that is why I didn't understand the 0, I assumed it would stay Blank/Empty with the IFERROR(.................),"")

 

[Jonmo1]

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?

 

[CypressHCC]

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?

It is not a 0 it is an actual result (i.e.) Mr. Brown, however, if there is no value it is blank.

 

[Joe4]

It is not a 0 it is an actual result (i.e.) Mr. Brown, however, if there is no value it is blank.

Based on how you wrote your formula, it might not actually be looking up the value you think it is.
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).