Using IF Formula for Vlookup
 

Hi

I am using Vlookup formula refering to a 2 column table of which the left is
a code and the right colmn is the amount. if the code is not found I want it
to come up zero rather than N/A, so if the table is updated in future with
different codes it will still come up correctly. Maybe it can highlight the
cell a different colour if it cannot find that code in the table?

Using IF Formula for Vlookup
 

In Excel2003:
=IF(ISERROR(VLOOKUP(yourarguments)),"",VLOOKUP(you rarguments))
In Excel2007 there is a more compact solution.

For highlighting apply
=ISERROR(VLOOKUP(yourarguments)) as conditional formatting formula!

Regards,
Stefi

€žGeoff€� ezt Ã*rta:

Hi

I am using Vlookup formula refering to a 2 column table of which the left is
a code and the right colmn is the amount. if the code is not found I want it
to come up zero rather than N/A, so if the table is updated in future with
different codes it will still come up correctly. Maybe it can highlight the
cell a different colour if it cannot find that code in the table?

Using IF Formula for Vlookup
 

Thanks

the value is coming up "TRUE" rather than zero.

Is there a way of excel putting zero instead.
Cheers

"Stefi" wrote:

In Excel2003:
=IF(ISERROR(VLOOKUP(yourarguments)),"",VLOOKUP(you rarguments))
In Excel2007 there is a more compact solution.

For highlighting apply
=ISERROR(VLOOKUP(yourarguments)) as conditional formatting formula!

Regards,
Stefi

€žGeoff€� ezt Ã*rta:

Hi

I am using Vlookup formula refering to a 2 column table of which the left is
a code and the right colmn is the amount. if the code is not found I want it
to come up zero rather than N/A, so if the table is updated in future with
different codes it will still come up correctly. Maybe it can highlight the
cell a different colour if it cannot find that code in the table?

Using IF Formula for Vlookup
 

=IF(ISERROR(VLOOKUP(yourarguments)),0,VLOOKUP(your arguments))
This version returns 0 if code is not found.
For highlighting
=ISERROR(VLOOKUP(yourarguments)) returns TRUE if code is not found as
required in conditional formatting.

Stefi


€žGeoff€� ezt Ã*rta:

Thanks

the value is coming up "TRUE" rather than zero.

Is there a way of excel putting zero instead.
Cheers

"Stefi" wrote:

In Excel2003:
=IF(ISERROR(VLOOKUP(yourarguments)),"",VLOOKUP(you rarguments))
In Excel2007 there is a more compact solution.

For highlighting apply
=ISERROR(VLOOKUP(yourarguments)) as conditional formatting formula!

Regards,
Stefi

€žGeoff€� ezt Ã*rta:

Hi

I am using Vlookup formula refering to a 2 column table of which the left is
a code and the right colmn is the amount. if the code is not found I want it
to come up zero rather than N/A, so if the table is updated in future with
different codes it will still come up correctly. Maybe it can highlight the
cell a different colour if it cannot find that code in the table?

Using IF Formula for Vlookup
 

I think that for Excel 2007, Stefi intended to suggest
=IFERROR(VLOOKUP(yourarguments),"") as an alternative to Excel 2003's
=IF(ISERROR(VLOOKUP(yourarguments)),"",VLOOKUP(you rarguments))

If you want a zero as the result in the error condition, replace "" in the
formula by 0.
--
David Biddulph

"Geoff" wrote in message
...
Thanks

the value is coming up "TRUE" rather than zero.

Is there a way of excel putting zero instead.
Cheers

"Stefi" wrote:

In Excel2003:
=IF(ISERROR(VLOOKUP(yourarguments)),"",VLOOKUP(you rarguments))
In Excel2007 there is a more compact solution.

For highlighting apply
=ISERROR(VLOOKUP(yourarguments)) as conditional formatting formula!

Regards,
Stefi

"Geoff" ezt írta:

Hi

I am using Vlookup formula refering to a 2 column table of which the
left is
a code and the right colmn is the amount. if the code is not found I
want it
to come up zero rather than N/A, so if the table is updated in future
with
different codes it will still come up correctly. Maybe it can highlight
the
cell a different colour if it cannot find that code in the table?

Using IF Formula for Vlookup
 

Thanks very helpful


"David Biddulph" wrote:

I think that for Excel 2007, Stefi intended to suggest
=IFERROR(VLOOKUP(yourarguments),"") as an alternative to Excel 2003's
=IF(ISERROR(VLOOKUP(yourarguments)),"",VLOOKUP(you rarguments))

If you want a zero as the result in the error condition, replace "" in the
formula by 0.
--
David Biddulph

"Geoff" wrote in message
...
Thanks

the value is coming up "TRUE" rather than zero.

Is there a way of excel putting zero instead.
Cheers

"Stefi" wrote:

In Excel2003:
=IF(ISERROR(VLOOKUP(yourarguments)),"",VLOOKUP(you rarguments))
In Excel2007 there is a more compact solution.

For highlighting apply
=ISERROR(VLOOKUP(yourarguments)) as conditional formatting formula!

Regards,
Stefi

"Geoff" ezt Ã*rta:

Hi

I am using Vlookup formula refering to a 2 column table of which the
left is
a code and the right colmn is the amount. if the code is not found I
want it
to come up zero rather than N/A, so if the table is updated in future
with
different codes it will still come up correctly. Maybe it can highlight
the
cell a different colour if it cannot find that code in the table?