need Lookup table to return null or zero

I need the lookup fucntion to return a null or a zero when it cannot
find the value required? Currently it is returning the next nearest
value!!

For example:
Col A Col B
07-Jul-05 1
08-Jul-05 4
11-Jul-05 3
12-Jul-05 3

If I lookup for 9-Jul-05 It will return "4" where I need a 0 or null!!

Must be an easy one but I have gone cross eyed!! Chairs

=IF(COUNTIF(A1:A4,--"9-Jul-05"),VLOOKUP(--"9-Jul-05",A1:B4,2,0),0)

=IF(ISNA(VLOOKUP(--"9-Jul-05",A1:B4,2,0)),0,VLOOKUP(--"9-Jul-05",A1:B4,2,0))

Regards,
KL

"simbob" <simon.flowers@networkrail.co.uk> wrote in message
news:1127464119.297389.32740@f14g2000cwb.googlegroups.com...
>I need the lookup fucntion to return a null or a zero when it cannot
> find the value required? Currently it is returning the next nearest
> value!!
>
> For example:
> Col A Col B
> 07-Jul-05 1
> 08-Jul-05 4
> 11-Jul-05 3
> 12-Jul-05 3
>
> If I lookup for 9-Jul-05 It will return "4" where I need a 0 or null!!
>
> Must be an easy one but I have gone cross eyed!! Chairs
>

=IF(ISNA(MATCH(D1,A:A,0)),0,INDEX(B:B,MATCH(D1,A:A,0)))

assuming test date is in D1

--
HTH

Bob Phillips

"simbob" <simon.flowers@networkrail.co.uk> wrote in message
news:1127464119.297389.32740@f14g2000cwb.googlegroups.com...
> I need the lookup fucntion to return a null or a zero when it cannot
> find the value required? Currently it is returning the next nearest
> value!!
>
> For example:
> Col A Col B
> 07-Jul-05 1
> 08-Jul-05 4
> 11-Jul-05 3
> 12-Jul-05 3
>
> If I lookup for 9-Jul-05 It will return "4" where I need a 0 or null!!
>
> Must be an easy one but I have gone cross eyed!! Chairs
>

Dear Simbob,

Your Vlookup formula has to be as follows:

VLOOKUP(C10,C7:D8,2,TRUE)

The true in the formula gives an "exact match"

Since there is no exact match, the value will be an error value and not 4

C10 houses the date you are searching for
C7:D8 is the table range

As of now the result is after assuming that the last argument is "false"

"simbob" wrote:

> I need the lookup fucntion to return a null or a zero when it cannot
> find the value required? Currently it is returning the next nearest
> value!!
>
> For example:
> Col A Col B
> 07-Jul-05 1
> 08-Jul-05 4
> 11-Jul-05 3
> 12-Jul-05 3
>
> If I lookup for 9-Jul-05 It will return "4" where I need a 0 or null!!
>
> Must be an easy one but I have gone cross eyed!! Chairs
>
>

> The true in the formula gives an "exact match"

I thought it was exactly the opposite :-)

KL

Let A2:B5 house the sample table you provided. If this table is sorted
in ascending order and you want to effect an exact match and 0 upon failure:

=IF(VLOOKUP(Date,$A$2:$A$5,1,1)=Date,VLOOKUP(Date,$A$2:$B$5,2,1),0)

If A2:B5 is unsorted...

=IF(ISNUMBER(MATCH(Date,$A$2:$A45,0)),VLOOKUP(Date,$A$2:$B$5,2,0),0)

simbob wrote:
> I need the lookup fucntion to return a null or a zero when it cannot
> find the value required? Currently it is returning the next nearest
> value!!
>
> For example:
> Col A Col B
> 07-Jul-05 1
> 08-Jul-05 4
> 11-Jul-05 3
> 12-Jul-05 3
>
> If I lookup for 9-Jul-05 It will return "4" where I need a 0 or null!!
>
> Must be an easy one but I have gone cross eyed!! Chairs
>