Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

This question already has an answer here:

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?

share|improve this question

marked as duplicate by Scott, DragonLord, Hennes, Ellesa, oKtosiTe Mar 26 '13 at 8:59

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2 Answers 2

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))
share|improve this answer

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.

share|improve this answer

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