remove #NA from index match formula.

J

JBW

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,'0405spenddata'!$A$2:$A$4755&'0405spenddata'!$C$2:$C$4755,0),6)
 
N

Niek Otten

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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,'0405spenddata'!$A$2:$A$4755&'0405spenddata'!$C$2:$C$4755,0),6)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

index,match - how to avoid same lookup when duplicates present 1
Index Match & #values 0
Index / Match Formula Problem 4
Index and Match 8
Formula Using Vlookup & Match 3
Index Match Inconsistencies 1
Or Function and #NA 2
Match and Vlookup issue 2

Top