VLOOKUP and IF statements

Hi there,

Once again, I would like to tap the infinite knowledge of Excel out there.

I am using a VLOOKUP function to bring in dates from another worksheet. I want my dates to end up in the 8/04/04 format. The trouble is that because I am using VLOOKUP with a sheet with incomplete data, some values are zero, and others are #N/A. For example, I am trying to pull in the dates that a particular product has sold. If a product had not sold, its 'date' field would be blank, and VLOOKUP returns a zero value. If it's not in the table at all, VLOOKUP returns a #N/A value.

To combat the #N/A problem, I use this:
=IF(ISERROR(VLOOKUP(A2,Sheet3!$B:$P,4,FALSE)),0,VL OOKUP(A2,Sheet3!$B:$P,4,FALSE))

This formula prevents any #N/As from being displayed, and returns 0 instead. This is great for a lot of work that I do, but here, with date format, it returns 1/0/00 every time a zero value is recorded.

What I want to have occur is for my master sheet (the one that has the VLOOKUP) to either display a real date (5/21/01), or no date at all. 1/0/00 is probably the LEAST desirable value that could be in that cell. I am also happy with a '-' (the zero value in accounting format).

I am trying to use this with a macro that creates a report, part of which is this date field from the VLOOKUP. Because I do not sort by date in the end, I just see this litany of 1/0/00 results throughout my table.

Thus, is there a way I can use an IF statement (even with ISERROR) to keep a cell empty (or suppress its data) while retaining the date format? How does everyone else deal with zero values in their Date fields?

Thanks so much in advance!!!!

Hi
try:
=IF(ISNA(VLOOKUP(A2,Sheet3!$B:$P,4,FALSE)),"",IF(V LOOKUP(A2,Sheet3!$B:$
P,4,FALSE)="","",VLOOKUP(A2,Sheet3!$B:$P,4,FALSE)) )


--
Regards
Frank Kabel
Frankfurt, Germany


JAnderson wrote:
Hi there,

Once again, I would like to tap the infinite knowledge of Excel out
there.

I am using a VLOOKUP function to bring in dates from another
worksheet. I want my dates to end up in the 8/04/04 format. The
trouble is that because I am using VLOOKUP with a sheet with
incomplete data, some values are zero, and others are #N/A. For
example, I am trying to pull in the dates that a particular product
has sold. If a product had not sold, its 'date' field would be
blank, and VLOOKUP returns a zero value. If it's not in the table at
all, VLOOKUP returns a #N/A value.

To combat the #N/A problem, I use this:

=IF(ISERROR(VLOOKUP(A2,Sheet3!$B:$P,4,FALSE)),0,VL OOKUP(A2,Sheet3!$B:$P
,4,FALSE))

This formula prevents any #N/As from being displayed, and returns 0
instead. This is great for a lot of work that I do, but here, with
date format, it returns 1/0/00 every time a zero value is recorded.

What I want to have occur is for my master sheet (the one that has
the VLOOKUP) to either display a real date (5/21/01), or no date at
all. 1/0/00 is probably the LEAST desirable value that could be in
that cell. I am also happy with a '-' (the zero value in accounting
format).

I am trying to use this with a macro that creates a report, part of
which is this date field from the VLOOKUP. Because I do not sort by
date in the end, I just see this litany of 1/0/00 results throughout
my table.

Thus, is there a way I can use an IF statement (even with ISERROR) to
keep a cell empty (or suppress its data) while retaining the date
format? How does everyone else deal with zero values in their Date
fields?

Thanks so much in advance!!!!

Frank,

I think I may be learning from you. We came up with the same statement independently of one another, and it works like a charm. 1000 thanks!

"Frank Kabel" wrote:

Hi
try:
=IF(ISNA(VLOOKUP(A2,Sheet3!$B:$P,4,FALSE)),"",IF(V LOOKUP(A2,Sheet3!$B:$
P,4,FALSE)="","",VLOOKUP(A2,Sheet3!$B:$P,4,FALSE)) )


--
Regards
Frank Kabel
Frankfurt, Germany


JAnderson wrote:
Hi there,

Once again, I would like to tap the infinite knowledge of Excel out
there.

I am using a VLOOKUP function to bring in dates from another
worksheet. I want my dates to end up in the 8/04/04 format. The
trouble is that because I am using VLOOKUP with a sheet with
incomplete data, some values are zero, and others are #N/A. For
example, I am trying to pull in the dates that a particular product
has sold. If a product had not sold, its 'date' field would be
blank, and VLOOKUP returns a zero value. If it's not in the table at
all, VLOOKUP returns a #N/A value.

To combat the #N/A problem, I use this:

=IF(ISERROR(VLOOKUP(A2,Sheet3!$B:$P,4,FALSE)),0,VL OOKUP(A2,Sheet3!$B:$P
,4,FALSE))

This formula prevents any #N/As from being displayed, and returns 0
instead. This is great for a lot of work that I do, but here, with
date format, it returns 1/0/00 every time a zero value is recorded.

What I want to have occur is for my master sheet (the one that has
the VLOOKUP) to either display a real date (5/21/01), or no date at
all. 1/0/00 is probably the LEAST desirable value that could be in
that cell. I am also happy with a '-' (the zero value in accounting
format).

I am trying to use this with a macro that creates a report, part of
which is this date field from the VLOOKUP. Because I do not sort by
date in the end, I just see this litany of 1/0/00 results throughout
my table.

Thus, is there a way I can use an IF statement (even with ISERROR) to
keep a cell empty (or suppress its data) while retaining the date
format? How does everyone else deal with zero values in their Date
fields?

Thanks so much in advance!!!!