Hi

I have a VLOOKUP in calculation =VLOOKUP(D14,'[Week 2.xls]Daily Lines'!
$A$4:$Y$18,23,0)/1000.

Basically When looking up the value in D14 if it is not in the [Week
2.xls]Daily Lines' sheet I am getting the #n/a error. Basically I
would like to return a zero or blank if d14 is not in the table array.
Can you please help.

I am subtotalling data so the #n/a error knocks things out straight
away.

Thanks

Brian

Try it like this:

=IF(ISNA(VLOOKUP(D14,'[Week 2.xls]Daily Lines'!$A$4:$Y$18,23,0)),
0,VLOOKUP(D14,'[Week 2.xls]Daily Lines'!$A$4:$Y$18,23,0)/1000)

Will return zero instead of #N/A, which is fine for subtotals. You
might like to return "" instead.

Hope this helps.

Pete

On Mar 4, 9:47*am, "brian.bake...@googlemail.com"
<brian.bake...@googlemail.com> wrote:
> Hi
>
> I have a VLOOKUP in calculation =VLOOKUP(D14,'[Week 2.xls]Daily Lines'!
> $A$4:$Y$18,23,0)/1000.
>
> Basically When looking up the value in D14 if it is not in the [Week
> 2.xls]Daily Lines' sheet I am getting the #n/a error. Basically I
> would like to return a zero or blank if d14 is not in the table array.
> Can you please help.
>
> I am subtotalling data so the #n/a error knocks things out straight
> away.
>
> Thanks
>
> Brian

On 4 Mar, 09:50, Pete_UK <pashu...@auditel.net> wrote:
> Try it like this:
>
> =IF(ISNA(VLOOKUP(D14,'[Week 2.xls]Daily Lines'!$A$4:$Y$18,23,0)),
> 0,VLOOKUP(D14,'[Week 2.xls]Daily Lines'!$A$4:$Y$18,23,0)/1000)
>
> Will return zero instead of #N/A, which is fine for subtotals. You
> might like to return "" instead.
>
> Hope this helps.
>
> Pete
>
> On Mar 4, 9:47*am, "brian.bake...@googlemail.com"
>
>
>
> <brian.bake...@googlemail.com> wrote:
> > Hi
>
> > I have a VLOOKUP in calculation =VLOOKUP(D14,'[Week 2.xls]Daily Lines'!
> > $A$4:$Y$18,23,0)/1000.
>
> > Basically When looking up the value in D14 if it is not in the [Week
> > 2.xls]Daily Lines' sheet I am getting the #n/a error. Basically I
> > would like to return a zero or blank if d14 is not in the table array.
> > Can you please help.
>
> > I am subtotalling data so the #n/a error knocks things out straight
> > away.
>
> > Thanks
>
> > Brian- Hide quoted text -
>
> - Show quoted text -

Great stuff

Works a treat

Thanks for feeding back - glad to be of help.

Pete

On Mar 4, 10:50*am, "brian.bake...@googlemail.com"
<brian.bake...@googlemail.com> wrote:
> On 4 Mar, 09:50, Pete_UK <pashu...@auditel.net> wrote:
>
>
>
>
>
> > Try it like this:
>
> > =IF(ISNA(VLOOKUP(D14,'[Week 2.xls]Daily Lines'!$A$4:$Y$18,23,0)),
> > 0,VLOOKUP(D14,'[Week 2.xls]Daily Lines'!$A$4:$Y$18,23,0)/1000)
>
> > Will return zero instead of #N/A, which is fine for subtotals. You
> > might like to return "" instead.
>
> > Hope this helps.
>
> > Pete
>
> > On Mar 4, 9:47*am, "brian.bake...@googlemail.com"
>
> > <brian.bake...@googlemail.com> wrote:
> > > Hi
>
> > > I have a VLOOKUP in calculation =VLOOKUP(D14,'[Week 2.xls]Daily Lines'!
> > > $A$4:$Y$18,23,0)/1000.
>
> > > Basically When looking up the value in D14 if it is not in the [Week
> > > 2.xls]Daily Lines' sheet I am getting the #n/a error. Basically I
> > > would like to return a zero or blank if d14 is not in the table array.
> > > Can you please help.
>
> > > I am subtotalling data so the #n/a error knocks things out straight
> > > away.
>
> > > Thanks
>
> > > Brian- Hide quoted text -
>
> > - Show quoted text -
>
> Great stuff
>
> Works a treat- Hide quoted text -
>
> - Show quoted text -