Can Vlookup return blanks? (Excel 2000)

Topic

New Reply

If I have a blank cell referenced as a result of a vlookup, Excel returns zero. Is there a way to change this behaviour so that blanks are returned as blanks rather than as zeros?

Thanks,
Archie

Reply | Quote

Replies

This works for me in XL2000:

=IF(VLOOKUP($A3,$A$1:$D$3,3,FALSE)=””,””,VLOOKUP($A3,$A$1:$D$3,3,FALSE))

Reply | Quote

This works for me in XL2000:

=IF(VLOOKUP($A3,$A$1:$D$3,3,FALSE)=””,””,VLOOKUP($A3,$A$1:$D$3,3,FALSE))

Reply | Quote

Does this work for you?

=IF(VLOOKUP(LookupValue,Array,2,FALSE)=0,””,VLOOKUP(LookupValue,Array,2,FALSE))

Reply | Quote

Does this work for you?

=IF(VLOOKUP(LookupValue,Array,2,FALSE)=0,””,VLOOKUP(LookupValue,Array,2,FALSE))

Reply | Quote

You can never have a formula return a blank. A blank cell means the cell is empty. If you have a formula in a cell, the cell is not blank.

Depending on what you need you can use what Michael suggested:
=IF(VLOOKUP($A3,$A$1:$D$3,3,FALSE)=””,””,VLOOKUP($A3,$A$1:$D$3,3,FALSE))
Which will give a null string (“”) if the “lookup cell” is blank or it returns a result of a null string.

Or even Paul’s formula:
=IF(VLOOKUP(LookupValue,Array,2,FALSE)=0,””,VLOOKUP(LookupValue,Array,2,FALSE))
Which will give a null string (“”) if the “lookup cell” is blank or it returns a result of zero.

If you want the results to be ignored in a chart, you must return a #N/A error, which a chart will ignore. A null or a 0 (or any other error) will plot as a zero on the chart. You can not simulate a truly “blank” entry in a plot with a formula.

Both problems have their downside. In Michael’s both null and blanks will return the same thing, and in Paul’s both zero and blanks will return the same thing.
If you want to return a null for null string, a zero for a zero, and something else (for example, #N/A error) for a blank, you must use a combination of the different formulas:
=IF(AND(VLOOKUP(LookupValue,Array,2,FALSE)=0,VLOOKUP(LookupValue,Array,2,FALSE)=””),NA(),VLOOKUP(LookupValue,Array,2,FALSE))

This will only return the #N/A error (or whatever) when the cell is blank (since that is the only time the cell can be = null and equal to zero). This is probably the closest you can get to returning a “blank”. The only other way is to put a result in the formula above, and afterwards write a macro to “clear” the cells contents if it has that value. (but this would remove the formula.)

Steve

Reply | Quote

Instead of doing 2 vlookups to check, it hit me on the way to work, just use ISBLANK
=IF(isblank(VLOOKUP(LookupValue,Array,2,FALSE)),na(),VLOOKUP(LookupValue,Array,2,FALSE))

The other alternative which should be mentioned is to replace all the blanks in the data with whatever value you want it to return. This will eliminate the need to do the IF statement at all and you can just use the VLOOKUP by itself

Steve

Reply | Quote

Thanks Steve, Paul & Michael. I needed to differentiate between null values and zero values in source cells, and your insight has allowed my to do this.

Cheers,
Archie

Reply | Quote

Thanks Steve, Paul & Michael. I needed to differentiate between null values and zero values in source cells, and your insight has allowed my to do this.

Cheers,
Archie

Reply | Quote

Instead of doing 2 vlookups to check, it hit me on the way to work, just use ISBLANK
=IF(isblank(VLOOKUP(LookupValue,Array,2,FALSE)),na(),VLOOKUP(LookupValue,Array,2,FALSE))

The other alternative which should be mentioned is to replace all the blanks in the data with whatever value you want it to return. This will eliminate the need to do the IF statement at all and you can just use the VLOOKUP by itself

Steve

Reply | Quote

You can never have a formula return a blank. A blank cell means the cell is empty. If you have a formula in a cell, the cell is not blank.

Depending on what you need you can use what Michael suggested:
=IF(VLOOKUP($A3,$A$1:$D$3,3,FALSE)=””,””,VLOOKUP($A3,$A$1:$D$3,3,FALSE))
Which will give a null string (“”) if the “lookup cell” is blank or it returns a result of a null string.

Or even Paul’s formula:
=IF(VLOOKUP(LookupValue,Array,2,FALSE)=0,””,VLOOKUP(LookupValue,Array,2,FALSE))
Which will give a null string (“”) if the “lookup cell” is blank or it returns a result of zero.

If you want the results to be ignored in a chart, you must return a #N/A error, which a chart will ignore. A null or a 0 (or any other error) will plot as a zero on the chart. You can not simulate a truly “blank” entry in a plot with a formula.

Both problems have their downside. In Michael’s both null and blanks will return the same thing, and in Paul’s both zero and blanks will return the same thing.
If you want to return a null for null string, a zero for a zero, and something else (for example, #N/A error) for a blank, you must use a combination of the different formulas:
=IF(AND(VLOOKUP(LookupValue,Array,2,FALSE)=0,VLOOKUP(LookupValue,Array,2,FALSE)=””),NA(),VLOOKUP(LookupValue,Array,2,FALSE))

This will only return the #N/A error (or whatever) when the cell is blank (since that is the only time the cell can be = null and equal to zero). This is probably the closest you can get to returning a “blank”. The only other way is to put a result in the formula above, and afterwards write a macro to “clear” the cells contents if it has that value. (but this would remove the formula.)

Steve

Reply | Quote