Thread: Adding string to match index

Hi all,

I'm trying to complete this formula. It works great. BUT, if the lookup value is not in the lookup array, it results in an NA. How do I make the value 0 if the lookup value is not in the lookup array? Is there an easier way to do this? Like I said, it works great unless there isn't a match. It takes only one to not match and I get the NA.

=INDEX($Q$5:$Q$13,(MATCH(A2,$F$5:$F$13,0)))+INDEX($AD$5:$AD$13,(MATCH(A2,$S$5:$S$13,0)))+INDEX($AT$5 :$AT$13,(MATCH(A2,$AF$5:$AF$13,0)))+INDEX($BG$5:$BG$13,(MATCH(A2,$AV$5:$AV$13,0)))

Thanks in advance!

Hi,

Try:

=IFERROR(INDEX($Q$5:$Q$13,(MATCH(A2,$F$5:$F$13,0)))+INDEX($AD$5:$AD$13,(MATCH(A2,$S$5:$S$13,0)))+INDEX($AT$5:$AT$13,(MATCH(A2,$AF$5:$AF$13,0)))+INDEX($BG$5:$BG$13,(MATCH(A2,$AV$5:$AV$13,0))),0)

Cheers,