cell format

  • Thread starter Thread starter tlee
  • Start date Start date
T

tlee

I have a vlookup formula.
=IF(ISERROR(VLOOKUP($A7,avail,7,FALSE)),"",VLOOKUP($A7,avail,7,FALSE))


the formula is looking for a date.
the formula works fine. but what is happening is that if it finds the item
and then column 7 is blank. the formula returns the following. 01/00/00


how can i get it so it doesnot return anything at all if there is nothing in
the column.
 
What do you want to see?

I'm guessing nothing:

=if(iserror(yourformula),"",if(yourformula="","",yourformula))

alternatively since you want to see "" in either case.

=IF(ISERROR(1/LEN(yourformula)),"",yourformula)
 
Just another 2 options to play with ..

a. Suppress extraneous zeros from showing in the sheet via:
Click Tools > Options > View tab > Uncheck "Zero values" > OK

b. Set another IF condition for the return to be null: ""
if the VLOOKUP returns a zero, i.e. try something like:

=IF(ISERROR(VLOOKUP($A7,avail,7,FALSE)),"",IF(VLOOKUP($A7,avail,7,FALSE)=0,"
",VLOOKUP($A7,avail,7,FALSE))

[ I'd usually go for the easier option (a) ]
 
Back
Top