remove #NA from index match formula.
 

The following formual looks up the result of two variables B3&E3 and returns
the spend number in £'s attributed to the site and vendor looked up. This is
being used for three yyears worth of data.

Trouble is, when there is no spend on a vendor in one year it returns #NA
which messes up rest of sheet, how can I get it to return the answer or a
ZERO if no answer is available please.


=INDEX('0405spenddata'!$A$2:$F$4755,MATCH(B3&E3,'0 405spenddata'!$A$2:$A$4755&'0405spenddata'!$C$2:$C $4755,0),6)

remove #NA from index match formula.
 

=IF(ISNA(YourFormula),"",YourFormula)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JBW" wrote in message ...
| The following formual looks up the result of two variables B3&E3 and returns
| the spend number in £'s attributed to the site and vendor looked up. This is
| being used for three yyears worth of data.
|
| Trouble is, when there is no spend on a vendor in one year it returns #NA
| which messes up rest of sheet, how can I get it to return the answer or a
| ZERO if no answer is available please.
|
|
| =INDEX('0405spenddata'!$A$2:$F$4755,MATCH(B3&E3,'0 405spenddata'!$A$2:$A$4755&'0405spenddata'!$C$2:$C $4755,0),6)

remove #NA from index match formula.
 

perfect, thankyou

"Niek Otten" wrote:

=IF(ISNA(YourFormula),"",YourFormula)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JBW" wrote in message ...
| The following formual looks up the result of two variables B3&E3 and returns
| the spend number in £'s attributed to the site and vendor looked up. This is
| being used for three yyears worth of data.
|
| Trouble is, when there is no spend on a vendor in one year it returns #NA
| which messes up rest of sheet, how can I get it to return the answer or a
| ZERO if no answer is available please.
|
|
| =INDEX('0405spenddata'!$A$2:$F$4755,MATCH(B3&E3,'0 405spenddata'!$A$2:$A$4755&'0405spenddata'!$C$2:$C $4755,0),6)