Homework Review

Here are my solutions from last week’s post. Did you happen to do something different?

Split the following strings into columns by special characters (:, -, ‘,”):

1. Here are three states that begin with M: Michigan, Mississippi, and Maine

Cell A3: =SPLIT(A2,“:”);

Cell C3: =SPLIT(B3,“,”)

2. This was first said by Shakespeare: “To thine own self be true.”

Cell A7: =SPLIT(A6,“:”)

Cell C7: =SPLIT(B7,“””)”)

3. D’Angelo Russell gets revenge on Bruce Brown: ‘Jokic ain’t there next to you today’

Cell A11: =SPLIT(A10,“:”)

Cell C11: =SPLIT(B11,“””)

4. Warning Label: Warning: Choking Hazard – Small Parts. Not for children under 3 years

Cell A15: =SPLIT(A14,“:”)

Cell D15: =SPLIT(C15,“-“)

5. Michelle drives a Jaguar named ‘Jessie’; Sonya drives either a Porsche, Mercedes, or BMW named one of the following: ‘Samantha’, ‘Miranda’, or ‘Charlotte’
   1. Hint: You will need the following formulas: SPLIT, ARRAYFORMULA, and CONCATENATE.

Cell A20: =SPLIT(A18,ARRAYFORMULA(CONCATENATE(“””,“;”,“,”,“:”)),1,1)

Index-Match

Ahhhhh. Index-Match. One of the most feared concepts for beginner/intermediate Excel users. The fear that is attached to this collection of functions is overwhelming. Why, though? Why do you think someone would be scared of a concept if they haven’t even used it before? Why would someone be scared of something that everyone tells them to avoid? Are these rhetorical questions genuinely not answering themselves? I believe that is it though- there’s a fear around this collection of functions because people haven’t been instructed to use them in a welcoming and comforting way. There’s a few ways that I tend to generalise this concept in my head, but I’ll take you through some examples that might help explain it.

Impetus

Why would someone even want to use Index-Match(-Match)? Index-Match is a series of functions that allows you to look up two different variables simultaneously. This reduces a user’s manual searching time and improves calculation, or analysis, time.

Definitions

INDEX

• Excel Formula: INDEX(reference, [row], [column])
• Input:
  • Reference: The array of cells to be offset into.
  • [row] The number of offset rows
  • [column]: The number of offset columns
• Output:
  • Returns the content of a cell specified by row and column offset.

So, what does this really mean? Let’s look at the sample data used in a blog post from a few weeks ago. Here’s a subset of the data that I’ve copied and pasted into this new spreadsheet for this week’s blog:

https://docs.google.com/spreadsheets/d/1f_4RC-Thrz3R2bUQnV45z3JYnl7sPPZnJDcdqixfuSA/edit

In cell A13, I’m going to type INDEX() and enter the table coordinates without the row or column information:

Rows

INDEX() creates the entire table. Now, let’s enter in 1 into the row attribute of the INDEX function:

This is showing us the first row of the table. Let’s replace 1 with 5:

This is showing us the fifth row of the table.

Columns

Let’s take out the row attribute value and put in 1 for the column:

This is showing us the first column of the table. Same as the rows, let’s replace the column value 1 for 5:

This is showing us the fifth column of the table.
Now, if I were to say, what is the value in the cell: fourth row down (including the header) and sixth column along? See if you can figure out what the value will be and then check with the formula =INDEX(A1:W11,4,6):

You have now understood how to use the INDEX function. Good stuff! It is using coordinates that you are feeding it to respond with a value. Can you see how useful this could be for future projects? Can you also see the potential downfall for this? I imagine this kind of scenario:

Completing this data analysis but then not looking at the dataset for a few months and then coming back to it, only to attempt to figure out what the 4th row and 6th columns meant.

To avoid this kind of confusion for future review or analysis, let’s have a look at how we can include MATCH into this function.

MATCH

• Excel Formula: MATCH(search_key, range, [search_type])
• Input:
  • search_key: The value to search for,
  • range: The one-dimensional array to be searched, i.e. this means to input either a column or a row,
  • search_type – [optional]: The search method:
    • 1 (default) finds the largest value less than or equal to search_key when range is sorted in ascending order.
    • 0 finds the exact value when range is unsorted.
    • -1 finds the smallest value greater than or equal to search_key when range is sorted in descending order.
• Output:
  • Returns the relative position of an item in a range that matches a specified value. This means that it is outputting the value of particular coordinates that you feed it.

So, what does this all really mean?

Rows

I want to find out which row Kobe Byrant is in. To find this, I am going to type the following code into cell A16: =MATCH(A5,A1:A11,0) (Also, LOL at the colours correct for the Excel function.)

So, what MATCH is doing is:

1. Take the row in A5,
2. Counting how many rows down it is (in the range A1 to A11), and
3. Returning the exact match for that function.

Let’s try it for his name now:

So, what MATCH is doing is:

1. Take the row in B5,
2. Counting how many rows down it is (in the range B1 to B11), and
3. Returning the exact match for that function.

I want to try something a little different now. Let’s type in ‘Kobe Bryant’ into cell A15 and set the search_key to cell A15:

Can you see what’s happening here? The search_key is now becoming dynamic and we can set anything in A15 to be searched. That’s pretty cool.

Do you remember talking about wildcards with regards to VLOOKUP? Let’s say that I didn’t want to type in Kobe Bryant’s full name. I’m going to add the wildcards “*” to the beginning and end of the A15 search_key:

Now we’re stepping into even cooler territory. We have just entered ‘Kobe’ and the MATCH function has still returned the accurate row that he is located in. Guess what- you have just learned how to use INDEX-MATCH. Congratulations! Seriously, huge congrats! There are a PLETHORA of people online who haven’t been able to understand or crack this code.

But, let’s take INDEX(MATCH()) a step further to INDEX(MATCH(),MATCH()). To do this, let’s understand the MATCH function.

Columns

We’ve got the whole ‘find the correct row (number)’ thing down. Let’s get the whole ‘find the correct column (number)’ thing. 

Similar to the row, I want to find out which column the PTS is in. Look, obviously, we can just count to the PTS column:

# = 1, PLAYER = 2, GP = 3, MIN = 4, and PTS = 5. But, let’s consider what happens when we blow out this really simple example into a table of big data; 782,035 rows * 983,100 columns. That’s a lot of data to navigate through, especially manually. This is teaching us how we can extrapolate (well, interpolate) this template from a 11*23 dataset to the hyperbolic example above.

Let’s find which column the ‘PTS’ is in using in A19; =MATCH(E1,A1:W1,0):

So, what MATCH is doing is:

1. Take the column in E1,
2. Counting how many columns cross (from the right) it is (in the range A1 to W1), and
3. Returning the exact match for that function.

Let’s try using the same approach we did for Kobe’s name. I’m going to type in ‘PTS’ into cell A18 and set the search_key to cell A18:

Can you also see what’s happening here, similarly to the row scenario? The search_key is now becoming dynamic and we can set anything in A18 to be searched. That’s also pretty cool.

Now I think it’s time to put this whole INDEX(MATCH(),MATCH()) thing to its use. Let’s say we’re interested in typing in particular players and seeing how many points they have scored in this dataset.

Think about how your brain processes this question whilst you’re analysing the table. Think about each step that you take. Write them down if you want:

• How many points (PTS) did Kobe Bryant score during these games?
• How many points (PTS) did Kevin Durant score during these games?
• What about Dirk Nowitzki?

Hopefully, your brain followed something like this sequence of steps:

1. What’s the data table I need to look at?,
2. Locate the row with Kobe Bryant,
3. Place my finger on it,
4. Locate the PTS column,
5. Place my finger on it,
6. Move my PTS finger down,
7. Move my Kobe finger across,
8. Stop when they match on the one cell.

That is exactly what we’re code with INDEX(MATCH(),MATCH()). So, let’s do it:

I have entered ‘Kobe Bryant’ into cell C15. I am entering the INDEX-MATCH-MATCH code into D15. Let’s review those previous steps:

1. What’s the data table I need to look at? (A1:W11),

2. Locate the row with Kobe Bryant (MATCH(C15,B1:B11,0)),

3. Place my finger on it,
4. Locate the PTS column (MATCH(D14,A1:W1,0)),

5. Place my finger on it,
6. Move my PTS finger down,
7. Move my Kobe finger across,
8. Stop when they match on the one cell.

There we go. Kobe Bryant scored 33,643 points during these seasons. Is that the number you got from before? Let’s find out how many Kevin Durant scored. Can you guess what we need to do?

That’s right. We just need to write ‘Kevin Durant’ in place of ‘Kobe Bryant’. Durant has 27,481 cumulative points. Now to check on Dirk’s:

He’s got 31,560 in his back pocket.

There’s two more things that I want to do before I call this example good.

Absolute References

I’m going to place the three players below one another, starting with Kobe, from C15 to C17, then copy the formula down. To maintain the data’s accuracy though, I want to make sure that I don’t shift the columns, rows, and (index) table around:

=INDEX($A$1:$W$11,MATCH(C15,$B$1:$B$11,0),MATCH(D$14,$A$1:$W$1,0))

Now I’m going to copy the formula down:

And there we go.

Wildcards

Remember how I mentioned how lazy efficient I was with coding? Let’s add some wildcards to the MATCH functions because I can’t be bothered typing in the player’s names each time enjoy the efficiency that coding in Excel allows you:

3 points for efficiency (And 1 from inside the perimeter, even).

Extra Bit

You may have noticed before that I didn’t write $D$14 in the second MATCH function under the ‘Absolute References’ portion. I didn’t include it because I was thinking ahead to this part of the post. Let’s say I want to get the following stats for the three listed players:

• PTS,
• GP,
• MIN, and
• AST

I need to adjust the following code:

=INDEX($A$1:$W$11,MATCH(“*”&C15&“*”,$B$1:$B$11,0),MATCH(D$14,$A$1:$W$1,0))

So that the C15 portion stays on column C:

=INDEX($A$1:$W$11,MATCH(“*”&$C15&“*”,$B$1:$B$11,0),MATCH(D$14,$A$1:$W$1,0))

Now, I can copy the functions from D15:D17 across to the right:

Using INDEX(MATCH(),MATCH()), it has allowed us to gather a subset of the data, based on the specific variables we are interested in.

Let’s see what this looks like in other examples.

Examples

Supermarket

Here is another example of how my brain thinks about INDEX(MATCH(),MATCH()). Below is a table of data regarding some supermarket inventory:

If I want to see information about two variables, I can use INDEX(MATCH(),MATCH()) to help me do it quickly:

For this example, I am using a lookup for the name of the product and its availability. This shows me at a moment’s notice whether I need to place an order or consider it at a different time. I could begin to integrate an IF statement with this INDEX-MATCH-MATCH function to assist me with automating this process.

Student Grades

One last example is monitoring and assessing student grades (trite, I know). Here is a table of data with student names, their 3 pieces of assessment, associated weightings for their overall grade, the grade percentage, and their assigned grade letter.

I have also created a marks/grade letter table for the matching process:

The following below allows me to take the entire table into consideration and even allows for variable changes. This means that I can search for any variable in the top row, i.e. Final Assessment, Grade%, Grade Letter (pictured), etc.

=INDEX(A2:I10,MATCH(K3,A2:A10,0),MATCH(L2,A1:I1,0))

Homework

Make a INDEX(MATCH(),MATCH()) formula using the following film data:

https://docs.google.com/spreadsheets/d/1f_4RC-Thrz3R2bUQnV45z3JYnl7sPPZnJDcdqixfuSA/edit; 4 – Film.

How creative can you get?