Scenario and Index Table in Excel

Originally Posted by alive555

yes if necessary. prefer not though.

cant this be not be done either with index table or scenario ?

thanks for help

With helper ranges in G2:R3 and U2:AF3 of dATA sheet containing these formulas in H2:R3 and V2:AF3:

Formula:

=IF(INDEX(Cashflow_YR!E$5,,),INDEX(Cashflow_YR!E$5,,),"")

and

Formula:

=IF(INDEX(Cashflow_YR!E$11,,),INDEX(Cashflow_YR!E$11,,),"")

then this formula in D16:D17

Formula:

=IRR(INDEX(dATA!$G$2:$R$3,MATCH($C16,dATA!$B$2:$B$3,0),),13)

and this one in E16:E17 of Cashflow_YR:

Formula:

=IRR(INDEX(dATA!$U$2:$AF$3,MATCH($C16,dATA!$B$2:$B$3,0),),13)

I believe these do what you want. Please let us know.

See attached.