In this workout, we'll use the match function as our scenario picker. I'm going to use the words 'Best Case', then go looking for them within an array and then use that to return one of the numbers, 10%, 6% or 3%. So we start, let's write in the match function and it asks, what is your lookup value? What do you want to look for? I want to look for the words 'Best Case'. It then asks your lookup array. Where do you want to go looking for it? I want to go looking for it in sales. B8 to B10. Lastly, it asks for what type. You could have less than or greater than, very simple if you are using numbers. However, we're looking for text and we therefore just want an exact match. So it now tells me that best case was the first of those three cells. I can change best case to base case and it should tell me that it's the second and I can change base case to worst case, and it should tell me that that's the third. Great, so we're now able to automate that number 1, 2, 3. Let's change it back to best case and let's combine that with the choose function first of all. So the choose function starts off by asking, what's your index number? Well, my index number is going to be number one. We find that in cell C5. So I want to go for the first of my three values which are in C8, C9 and cell C10. That should return the 10% and it does. Let's combine the offset and match function.

This starts by asking for some kind of reference cell or starting point. I want to take the cell immediately above my numbers. It then asks, how many rows do you want to move down from that reference point? Well, that's my scenario picker. I want to move down one. It then asks, how many columns do you want to move to the right? I don't want to move any columns at all and I ignore the height and with arguments 'cause I just want to return the value from one cell. That should come up with 10% and it does. Lastly, let's put the index function together with the match. This starts by asking me, where do you want to look? I want to look in cells C8 to cell C10. It then asks, of those three, what's the row number you want to go looking for? That's my scenario picker with a match function in it. I want to look for the first one. Again, hopefully that's going to be 1% and it is. Now let's change that best case to a base case and we'll see if each of those three functions underneath changes and they do, fantastic. Best case change to base case, which was 6%. Now let's look for worst case and that changes to 3%.