Avoiding a #n/a error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

brian.baker13@googlemail.com - 04 Mar 2008 09:47 GMT

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

Reply to this Message

Pete_UK - 04 Mar 2008 09:50 GMT

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
>
[quoted text clipped - 12 lines]
>
> Brian

Reply to this Message

brian.baker13@googlemail.com - 04 Mar 2008 10:50 GMT

> Try it like this:
>
[quoted text clipped - 29 lines]
>
> - Show quoted text -

Great stuff

Works a treat

Reply to this Message

Pete_UK - 04 Mar 2008 10:56 GMT

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:

> > Try it like this:
>
[quoted text clipped - 35 lines]
>
> - Show quoted text -

Reply to this Message

Rate this thread: