cell format

[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.

[Dave Peterson]

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)

tlee wrote:
>
> 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.

--

Dave Peterson

[Max]

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) ]

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"tlee" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

[tlee]

work great. thanks for all the help.


"tlee" <[email protected]> wrote in message
news:[email protected]...
>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.
>