Scenario and Index Table in Excel

In D16

=IF($C16:$C17=$A$1,$B$6,"")

In E16

=IF($C16:$C17=$A$1,$B$12,"")

Drag Down as required

But then what is the use of the Drop Down?

It is, but I'm absolutely not understanding what your requirement is..

If you want values in all 4 cells, then what is the use of the Drop Down?

I guess you will have to be more clearer..

what i want is to see in the table in d16:e17 are the results of (in this case) 2 scenarios.

in scenario A - d16:e16


the IRR should be 3.72% and EIRR 6.89%

in scenario B - - d17:e17

the IRR should be -2.06 and EIRR 3.30%

The benefit of the drop down is simply to allow me to use 1 spreadsheet for each scenario and not have to have a separate one for each scenario .

if i have separate sheets i need to have separate links and that gets very messy. (my actual spreasheet have many links to other sources)

so i want 1 spreadsheet and 1 table of answers.

Are you fine with VBA?

Maybe -

Please Login or Register  to view this content.

See the file attached.

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:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

then this formula in D16:D17

Formula:

Please Login or Register  to view this content.

and this one in E16:E17 of Cashflow_YR:

Formula:

Please Login or Register  to view this content.

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

See attached.