Avoiding a #n/a error

[brian.baker13]

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

 

[Pete_UK]

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

 

[brian.baker13]

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

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.

Brian- Hide quoted text -

- Show quoted text -

Great stuff

Works a treat

 

[Pete_UK]

Thanks for feeding back - glad to be of help.

Pete

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

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 -