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