3

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?

3

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

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. – NetMage Oct 17 '17 at 19:02
  • Yes, I know. I mention that in my post. Did you read it all the way to the end, @NetMage. – teylyn Oct 17 '17 at 19:37
  • Yes, but why provide an answer that is exactly not what is asked for? – NetMage Oct 17 '17 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. – teylyn Oct 18 '17 at 0:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.