VLOOKUP Results - removing 0

CypressHCC

New Member
Joined
Oct 26, 2017
Messages
14
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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?
 
Upvote 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?

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
 
Upvote 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.
 
Last edited:
Upvote 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.
 
Upvote 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:
[COLOR=#333333]=IFERROR(VLOOKUP([/COLOR][COLOR=#ff0000]A:A[/COLOR][COLOR=#333333],Physician!A:P,5,FALSE),"")[/COLOR]
So, it should look something like:
Code:
[COLOR=#333333]=IFERROR(VLOOKUP([/COLOR][COLOR=#FF0000]A1[/COLOR][COLOR=#333333],Physician!A:P,5,FALSE),"")[/COLOR]
 
Upvote 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.

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(.................),"")
 
Upvote 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?
 
Last edited:
Upvote 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?

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

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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