Vlookup '0' and ''

[meridius10]

I have set up a vlookup table which feeds off lists in another worksheet. The vlookup formula is in the main table.

Prior to any additions the vlookup would enter a blank cell as "" (i.e.blank), however, after making additional entries the new additions are being reported as 0 (zero) in cells which do not (purposefully) include a variable.

I can't understand why this has happened and would prefer to have blank cells in those that have no variable.

The vlookup formulas are:

=VLOOKUP($D7,VLookupList1,2,FALSE)
=VLOOKUP($D7,VLookupList1,3,FALSE)
=VLOOKUP($D7,VLookupList1,4,FALSE)
=VLOOKUP($D7,VLookupList1,5,FALSE)

Any ideas?

 

[T. Valko]

I have set up a vlookup table which feeds off lists in another worksheet. The vlookup formula is in the main table.

Prior to any additions the vlookup would enter a blank cell as "" (i.e.blank), however, after making additional entries the new additions are being reported as 0 (zero) in cells which do not (purposefully) include a variable.

I can't understand why this has happened and would prefer to have blank cells in those that have no variable.

The vlookup formulas are:

=VLOOKUP($D7,VLookupList1,2,FALSE)
=VLOOKUP($D7,VLookupList1,3,FALSE)
=VLOOKUP($D7,VLookupList1,4,FALSE)
=VLOOKUP($D7,VLookupList1,5,FALSE)

Any ideas?

If the data type of the value being returned is TEXT then try this...

=T(VLOOKUP($D7,VLookupList1,2,0))

If the data type is numeric or variable then use this version...

=IF(VLOOKUP($D7,VLookupList1,2,0)="","",VLOOKUP($D7,VLookupList1,2,0))

 

[sulakvea]

by default vlookup converts blanks to zeros (unless the source data had user-generated blanks in them), like there was a formula like if (a then "" else b), or thru VBA, etc.

you can change blanks with ="", and then paste special as values. or you can change your vlookup - = if (vlookup=0,"",vlookup)

 

[meridius10]

Thanks -

=T(VLOOKUP($D7,VLookupList1,2,0))

This appears to have done the trick...

 

[T. Valko]

Thanks -

=T(VLOOKUP($D7,VLookupList1,2,0))

This appears to have done the trick...

You're welcome. Thanks for the feedback!