VLOOKUP
 

We have a sheet which contains a VLOOKUP. But when the date looked up on
another sheet finds a blank, the date returned onto the sheet with the
VLOOKUP command, comes up with the date: 00/01/1900. We'd like it just to
show a blank field.

This is the current formula: =VLOOKUP(A3,Offers!$B$2:$U$751,18,FALSE)

Thanks.

VLOOKUP
 

Use a Custom number format.

Select the cell and press Ctrl-1. On the Number tab choose Custom and in
the Style box type

m/d/yyyy;;""



"bollard" wrote:

We have a sheet which contains a VLOOKUP. But when the date looked up on
another sheet finds a blank, the date returned onto the sheet with the
VLOOKUP command, comes up with the date: 00/01/1900. We'd like it just to
show a blank field.

This is the current formula: =VLOOKUP(A3,Offers!$B$2:$U$751,18,FALSE)

Thanks.

VLOOKUP
 

Try it like this:
=IF(VLOOKUP(...)=0,"",VLOOKUP(...))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"bollard" wrote:
We have a sheet which contains a VLOOKUP. But when the date looked up on
another sheet finds a blank, the date returned onto the sheet with the
VLOOKUP command, comes up with the date: 00/01/1900. We'd like it just to
show a blank field.

This is the current formula: =VLOOKUP(A3,Offers!$B$2:$U$751,18,FALSE)

Thanks.

VLOOKUP
 

Hi Max

I tried this, but it didn't work. I also tried replacing the 0- with "" or "
", but that didn't work either.

Seemed such a neat idea too.

"Max" wrote:

Try it like this:
=IF(VLOOKUP(...)=0,"",VLOOKUP(...))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"bollard" wrote:
We have a sheet which contains a VLOOKUP. But when the date looked up on
another sheet finds a blank, the date returned onto the sheet with the
VLOOKUP command, comes up with the date: 00/01/1900. We'd like it just to
show a blank field.

This is the current formula: =VLOOKUP(A3,Offers!$B$2:$U$751,18,FALSE)

Thanks.

VLOOKUP
 

well, think it should have worked. "00/01/1900" is simply a zero returned by
the vlookup for blank cells where the formula cell is formatted as dd/m/yyyy.

Here's a quick sample to illustrate that it works:
http://cjoint.com/?ivpswpsoqg
bollard.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"bollard" wrote:
Hi Max

I tried this, but it didn't work. I also tried replacing the 0- with "" or "
", but that didn't work either.

Seemed such a neat idea too.

VLOOKUP
 

Hi Max

I just did it again and it works fine.

Many thanks.

"Max" wrote:

well, think it should have worked. "00/01/1900" is simply a zero returned by
the vlookup for blank cells where the formula cell is formatted as dd/m/yyyy.

Here's a quick sample to illustrate that it works:
http://cjoint.com/?ivpswpsoqg
bollard.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"bollard" wrote:
Hi Max

I tried this, but it didn't work. I also tried replacing the 0- with "" or "
", but that didn't work either.

Seemed such a neat idea too.

VLOOKUP
 

ok, good to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"bollard" wrote in message
...
Hi Max

I just did it again and it works fine.

Many thanks.