If formula displaying false

**msbing916** · 11-30-2009, 05:39 PM

Hello-
I am having two problems with a formula. The first being that when the first if condition is not met, it is displaying false. The formula should be set up with multiple if statements and then display 0 if all conditions are false. What am I missing? Why won't it work for all the conditions or display a 0?

Second problem is that when I try to add the last if formula to accommodate the drop down option of 60 months (5) excel is giving me an error in the formula. It doesn't matter if I type in the if statement or cut and paste. Why won't it work for this option.

A sample spreadsheet is attached. The formula is cell F6 highlighted in yellow.
Thanks in advance!

**NBVC** · 11-30-2009, 05:46 PM

Missing closing parentheses after each Index/Match combo.

Perhaps:

=IF(C12=2,INDEX(B31:B63,MATCH(B6,C31:C63)),IF(C12=3,INDEX(B31:B63,MATCH(B6,D31:D63)),IF(C12=4,INDEX(B31:B63,MATCH(B6,E31:E63),IF(C12=6,INDEX(B31:B63,MATCH(B6,F31:F63)),0)))))

**msbing916** · 11-30-2009, 05:53 PM

Wow, two birds with one formula!

I figured it had to be something obvious but my experience is limited with both index and matching formulas so I didn't catch it.
Thanks!!!!!

**NBVC** · 11-30-2009, 06:07 PM

That last one probable should have be IF(C12=5...)...

Another shorter way to do the same thing:

=LOOKUP(9.99999E+307,CHOOSE({1,2},0,INDEX(B31:B63,MATCH(B6,INDEX(C31:F63,0,C12-1)))))