Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula INDEX/MATCH | Excel Worksheet Functions | |||
index, match formula | Excel Worksheet Functions | |||
Formula using INDEX and MATCH | Excel Worksheet Functions | |||
INDEX MATCH formula | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions |