Using Vlookup wheb value missing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nir020 - 20 Mar 2006 17:18 GMT

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

Reply to this Message

Ardus Petus - 20 Mar 2006 17:42 GMT

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

I fear this is the only way

HTH
--
AP

> 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

Reply to this Message

Trevor Shuttleworth - 20 Mar 2006 17:44 GMT

Nick

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

Regards

Trevor

> How can you when using vlookup return a value of 0 rather than #N/A when
> the
[quoted text clipped - 3 lines]
>
> Nick

Reply to this Message

Jim Thomlinson - 20 Mar 2006 17:46 GMT

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))

Signature

HTH...

Jim Thomlinson

> 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

Reply to this Message

Tom Ogilvy - 20 Mar 2006 17:51 GMT

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

Signature

Regards,
Tom Ogilvy

> 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

Reply to this Message

Toppers - 20 Mar 2006 17:51 GMT

Use:

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

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

HTH

> 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

Reply to this Message