In this workout, we look at the index function. We start by typing in the function and it then asks us for a few things. The first of these is what is your array? Well, I want to choose from the three scenarios here. So we put some parameters. We put an outer limit upon which the scenario picker can choose from. It then asks me what row number do you want to go for? I want to go for the first one, I'm gonna hardcode in a one. It then asks me for an optional column number, how many columns do you want to go to the right? I don't want to go any columns to the right so I can just leave that out. I press enter and that gets me 10%. I can change the one to a two, and that will then get me the second of my scenarios, the base case, 6%. I can change the two to a three, and I get the third case. However much better if we have that scenario figure outside of the formula where we can see it. So my scenario picker is now going for number one, which will be the 10%. I could change the one to a two to a three and I can now choose between those different scenarios.

Now the index function is flexible. If I want to put an extra scenario in, I can. The only downside is that I can't put it at the bottom. I can see that that has not updated the array so only the three cells. However, if I insert a row within the array, then that will pick up the extra item. So let's put it in there. So alternative case 4%, and that has now come up. Let's check that numbers 1, 2, 3, and 4, all still works. So one gives me 10%, 2, 3, and lost the fourth one. The worst case gives me that 3%. If I did want to move a column to the right, then I would have to widen my array. So my array would have to be from C7 to D10.

The function then returns #REF, that's because I haven't told it which columns go for one or two. So if I go for one to start with, it gets me the 3%. If we add some extra numbers in here, I can now ask it to go to the second column, and it has picked up on that 4% there. If I change my scenario picker to one, it'll be 11%, two to the 7%, three the 5%, and four the 4%.