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

 

[CypressHCC]

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

but, I need to look at the whole column to find the match to the sheet with the data for the desired "returned" value. That is why I am using vlookup. What is FormR?

 

[NARAYANK991]

Hi ,

FormR is a forum member who has responded in posts 2 and 5.

The VLOOKUP function has 4 parameters , as in :

=VLOOKUP(a , b , c , d)

a is supposed to be a scalar value , which means either a single value or a reference to a single cell.

b is supposed to be a range , with one or more columns.

c is the number of the column which is to be used to return the value in the row corresponding to the row where the lookup value had a match. For example , if a range is E6:J22 , which is 17 rows and 6 columns wide , c can range from 1 to 6. Suppose a match is found in row 14 of the range , i.e. in cell E19 , and if c = 3 , then the value returned will be from cell G19.

The last parameter d is either TRUE or FALSE , or 1 or 0 ; TRUE or 1 mean the VLOOKUP will do an approximate match and return the nearest value if an exact match is not found. FALSE or 0 means the VLOOKUP will do an exact match , and return the #N/A error if no match is found.

 

[Joe4]

I need to look at the whole column to find the match to the sheet with the data for the desired "returned" value. That is why I am using vlookup.

I am not sure you have a full understanding of how VLOOKUP works.
Looking at the post above:

[COLOR=#333333]=VLOOKUP(a , b , c , d)[/COLOR]

VLOOKUP looks up the single value "a" in the range "b" and returns the value from column "c" (of your range "b").
If you want to look up multiple values from column A, you would use multiple VLOOKUPs (one for each value you are looking up).

Please look here for an explanation and examples: https://www.techonthenet.com/excel/formulas/vlookup.php

 

[CypressHCC]

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

What is FormR?

 

[CypressHCC]

I do understand how it works, I am not looking for multiple values, I am only concerned with the 0 as a value returning. Thank you

 

[Joe4]

I do understand how it works, I am not looking for multiple values, I am only concerned with the 0 as a value returning.

I am not trying to be argumentative, but I am not entirely convinced of that, because if it was true, you probably wouldn't be using A:A in the first argument, but rather the cell address of the one value you are trying to look up. I have never been on this board for over 15 years and have helped thousands of people, and have never seen anyone intentionally try to use a whole range in that argument. I am not sure what that would so - that might give unexpected results.
If you are looking up one value, this is the address of the value you are trying to lookup and match to your Physician's sheet.

Maybe try answering this question for us:
- What is the exact cell address of where you are placing this formula?
- Is it supposed to be looking up the value from column A of that same row?

 

[CypressHCC]

Hi ,

FormR is a forum member who has responded in posts 2 and 5.

The VLOOKUP function has 4 parameters , as in :

=VLOOKUP(a , b , c , d)

a is supposed to be a scalar value , which means either a single value or a reference to a single cell.

b is supposed to be a range , with one or more columns.

c is the number of the column which is to be used to return the value in the row corresponding to the row where the lookup value had a match. For example , if a range is E6:J22 , which is 17 rows and 6 columns wide , c can range from 1 to 6. Suppose a match is found in row 14 of the range , i.e. in cell E19 , and if c = 3 , then the value returned will be from cell G19.

The last parameter d is either TRUE or FALSE , or 1 or 0 ; TRUE or 1 mean the VLOOKUP will do an approximate match and return the nearest value if an exact match is not found. FALSE or 0 means the VLOOKUP will do an exact match , and return the #N/A error if no match is found.

Thank you!

 

[CypressHCC]

I am not trying to be argumentative, but I am not entirely convinced of that, because if it was true, you probably wouldn't be using A:A in the first argument, but rather the cell address of the one value you are trying to look up. I have never been on this board for over 15 years and have helped thousands of people, and have never seen anyone intentionally try to use a whole range in that argument. I am not sure what that would so - that might give unexpected results.
If you are looking up one value, this is the address of the value you are trying to lookup and match to your Physician's sheet.

Maybe try answering this question for us:
- What is the exact cell address of where you are placing this formula?
- Is it supposed to be looking up the value from column A of that same row?

I am not being argumentative, just wanting to understand the 0 in the return. I was taught by an Excel Expert awhile back that using A:A was acceptable given that the amount of rows can vary when pulling data.

So the ranges for me do not look out of place.

vlookup(A:A (for the tab I am in),Physician!A:P (tab the data I am matching as a range),7 (column to pull back),FALSE). It works fine if there is data in the cell, when the cell is empty is when I get the 0, even using the IFERRROR.

Not sure how else to explain, but I just want the blank to return a "null" value in the cell.

 

[Joe4]

I am not being argumentative, just wanting to understand the 0 in the return. I was taught by an Excel Expert awhile back that using A:A was acceptable given that the amount of rows can vary when pulling data.

We have a lot of actual Microsoft certified MVPs on this site, and I have never seen anyone recommend that. I suppose it might work, but it is very unorthodox.
What you say about the "amount of rows" is a true statement for the the range you are looking into, so you commonly see the third argument set up like that.
But understand that VLOOKUP is looking up a single value, the value you list in the first argument. So while it may work to use the whole range (I assume then it is probably just picking the value from the row that the formula is located in), it doesn't really follow the logic of the function. Search this site, or any tutorial on the VLOOKUP function, and you would be hard-pressed to find a single one that tells you to structure it that way.

It doesn't really matter that you don't know how many VLOOKUPs you may have, because Excel is smart enough to automatically adjust it for you.
So, if in the first row, you were looking up the value in cell A1, and you had this formula:

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

and you copy it down your column, Excel will adjust it so the next one looks like:

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

So there is no reason to use A:A, Excel already take care of it.

Back to your question, as long as none of your values of the column you are returning have a 0 in them, you can replace the zeroes that are being returned by the blanks by structuring the formula like this:

=IF(VLOOKUP(...)=0,"",VLOOKUP(...))

 

[CypressHCC]

We have a lot of actual Microsoft certified MVPs on this site, and I have never seen anyone recommend that. I suppose it might work, but it is very unorthodox.
What you say about the "amount of rows" is a true statement for the the range you are looking into, so you commonly see the third argument set up like that.
But understand that VLOOKUP is looking up a single value, the value you list in the first argument. So while it may work to use the whole range (I assume then it is probably just picking the value from the row that the formula is located in), it doesn't really follow the logic of the function. Search this site, or any tutorial on the VLOOKUP function, and you would be hard-pressed to find a single one that tells you to structure it that way.

It doesn't really matter that you don't know how many VLOOKUPs you may have, because Excel is smart enough to automatically adjust it for you.
So, if in the first row, you were looking up the value in cell A1, and you had this formula:

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

and you copy it down your column, Excel will adjust it so the next one looks like:

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

So there is no reason to use A:A, Excel already take care of it.

Back to your question, as long as none of your values of the column you are returning have a 0 in them, you can replace the zeroes that are being returned by the blanks by structuring the formula like this:

=IF(VLOOKUP(...)=0,"",VLOOKUP(...))

Thank you! I will give this a shot to see if it will return a blank for no value in the cell.