My vlookup call in an Excel 2010 spreadsheet is returning a value of 0 for values that are blank in the lookup data.
How can I force vlookup to return a blank when the data is blank, and 0 when the data is 0?
asked Mar 25, 2013 at 4:27
Add a comment
|
2 Answers
This method works, though it would certainly be nice if there was a better method:
=if(len(vlookup(a2,f$2:g$20,2))=0,"",vlookup(a2,f$2:g$20,2))
answered Mar 25, 2013 at 4:27
Be aware that vlookup without the fourth parameter (true or false) will default to "true" and will return an approximate match. Also the data table must be sorted ascending in column F, otherwise the results may be wrong. If you are after an exact match, you should use the "false" parameter.
That aside, you can compare the returned value with a 0, like
=if(vlookup(a2,f$2:g$20,2,false)=0,"",vlookup(a2,f$2:g$20,2,false))
The Len() function is not required.
As an alternative to the double Vlookup, you can format the results with custom format
0.00;-0.00;;@
This will format positive and negative numbers with two decimals and display text, but no zero values. Adjust the number format to your needs.
In both cases, a zero value will be suppressed, even if it is the actual value in the lookup table.
-
Your first alternative returns "" when the cell is zero, which is not what the OP asked for. Custom formatting will also not return a proper value.– NetMageOct 17, 2017 at 19:02
-
Yes, I know. I mention that in my post. Did you read it all the way to the end, @NetMage.– teylynOct 17, 2017 at 19:37
-
Yes, but why provide an answer that is exactly not what is asked for?– NetMageOct 17, 2017 at 22:55
-
It's easy to criticize, but much harder to provide an answer. Not every answer is a perfect match to the question and still those answers can contain helpful content. Can you just let it go? If not, I suggest you rack up some experience and learn what it's like to answer questions instead of just criticizing answers.– teylynOct 18, 2017 at 0:24
-