Using Vlookup wheb value missing

[nir020]

How can you when using vlookup return a value of 0 rather than #N/A when the
lookup value is not present in the table array?

Thanks

Nick

[Trevor Shuttleworth]

Nick

=IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))

Regards

Trevor


"nir020" <[email protected]> wrote in message
news:[email protected]...
> How can you when using vlookup return a value of 0 rather than #N/A when
> the
> lookup value is not present in the table array?
>
> Thanks
>
> Nick

[Ardus Petus]

=IFNA(Vlookup(),0,Vlookup())

I fear this is the only way

HTH
--
AP

"nir020" <[email protected]> a écrit dans le message de
news:[email protected]...
> How can you when using vlookup return a value of 0 rather than #N/A when
the
> lookup value is not present in the table array?
>
> Thanks
>
> Nick

[Jim Thomlinson]

My prefered way is to use an if function with countif to determine if the
vallue being looked up exists, something like this...

=IF(COUNTIF($D$1:$D$10, A1)=0,0, VLOOKUP(A1, $D$1:$E$10, 2, FALSE))
--
HTH...

Jim Thomlinson


"nir020" wrote:

> How can you when using vlookup return a value of 0 rather than #N/A when the
> lookup value is not present in the table array?
>
> Thanks
>
> Nick

[Tom Ogilvy]

=if(iserror(match(lookupvalue,firstcolumnofrange,0)),0,vlookup(lookupvalue,range,2,false))

--
Regards,
Tom Ogilvy


"nir020" wrote:

> How can you when using vlookup return a value of 0 rather than #N/A when the
> lookup value is not present in the table array?
>
> Thanks
>
> Nick

[Toppers]

Use:

C1 <lookup value>
A1:B20 <lookup table>

=IF(ISERROR(VLOOKUP(C1,A1:B20,2,FALSE)),0,VLOOKUP(C1,A1:B20,2,FALSE))


HTH

"nir020" wrote:

> How can you when using vlookup return a value of 0 rather than #N/A when the
> lookup value is not present in the table array?
>
> Thanks
>
> Nick