Thanks:  0
Likes:  0

# Thread: VLOOKUP Results - removing 0

1. ## VLOOKUP Results - removing 0

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

2. ## Re: VLOOKUP Results - removing 0

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?

3. ## Re: VLOOKUP Results - removing 0

Originally Posted by 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?
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

4. ## Re: VLOOKUP Results - removing 0

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.

5. ## Re: VLOOKUP Results - removing 0

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.

6. ## Re: VLOOKUP Results - removing 0

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!
Code:
`=IFERROR(VLOOKUP(A:A,Physician!A:P,5,FALSE),"")`
So, it should look something like:
Code:
`=IFERROR(VLOOKUP(A1,Physician!A:P,5,FALSE),"")`

7. ## Re: VLOOKUP Results - removing 0

Originally Posted by 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.
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(.................),"")

8. ## Re: VLOOKUP Results - removing 0

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?

9. ## Re: VLOOKUP Results - removing 0

Originally Posted by 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?
It is not a 0 it is an actual result (i.e.) Mr. Brown, however, if there is no value it is blank.

10. ## Re: VLOOKUP Results - removing 0

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).

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•