13. Building Multiple Scenarios Part 2

 
Subtitles Enabled

Sign up for a free trial to continue watching this lesson.

Free trial

Overview

In this lesson, we wrap up our scenario-building, by connecting our LIVE column to our scenario dropdown button. This will utilise the ROW, MATCH and INDEX functions

Lesson Notes

Linking live column to selected scenario

- For each cell in the live column, INDEX, MATCH, and ROW are used to find correct value
- The MATCH function selects the correct input column
- The ROW function selects the correct input row
- The INDEX function combines the previous functions to return correct value

Keyboard shortcuts

SHIFT + : Select next cell
F2: Jump back inside a formula
F4: Anchor cells
ALT + E , S , F: Paste formulas

Transcript

In the previous lesson, we created a dropdown that allowed us to pick our Live scenario, from pessimistic, base or optimistic. In this lesson, we're going to link our selected scenario to the live column so that it updates with the new variables when this scenario was changed. To do this, we'll use a combination of the match, row and index functions. Match will be used to find the correct column in our array. Row will be used to find the correct row and Index would accept both of these inputs to return the correct value to our live column. Let's first look at the match function. The match function accepts a lookup value and then returns that value's position within our array. Our selected scenario will be our lookup value and the match function will return its column number within our scenario array. Let's see it in action by writing, equals match, open the bracket and select the lookup value. Next, we'll select the lookup array, which will simply be my three scenarios and the match type which will be zero, because we'll look for an exact match. I'll then close the brackets and press Enter. And as you can see optimistic returns the value of three, base should return the value of two and pessimistic, a value of 1. Now let's move on to the Row function. This function simply returns the row of the current cell if you don't pass the function any value. So let's move to the cell H9 and I'll write, equals row and empty brackets. And this returns the value of nine because it's the ninth row of my spreadsheet. However, within my array, this row should correspond to row two. So to turn this number from nine to two, I'll jump back inside the formula and I'll subtract the row number that corresponds to seven from the row number that corresponds to nine. And the row number that corresponds to seven, is simply the row for H7. I'll then close the bracket and press Enter. For the remaining rows, I'd like this formula repeated but because I haven't anchored the original cell, you'll see when I copy and paste that it continues to calculate row two all the way down. So let's jump back into our formula, anchor this cell for H7 with F4 and now copy this formula which will give us the correct row number. I now combine the column number with the row number using the index function to give me the correct live values. So let's start in the first cell. I'll write equals index, open a bracket and select the array. I'll include the headers, I'll include all of the values and I'll press F4 to anchor this array. Next, I'll select the row number, which is simply two, and then the column number which I'll anchor with F4. I'll then close the bracket and press Enter. And as you can see this cell now returns the correct value for the pessimistic scenario. I'll then copy and paste for the remaining cells. If I now change my scenario, you should see the Live cells update accordingly which they do. We can now switch between scenarios and change the variables within our scenarios right from the control panel. This will save us a lot of time in the future and reduce the potential for errors. As an exercise try to combine the match function, the row function and the index function all in the one formula. I'll leave my answer in the after file below the video.