Master Microsoft Excel: Learn the Index Match Fast!

1. Introduction - The Index & Match Formulas: Hi everyone. I'm Jeremy, or as some call me Big dog. Welcome to my Skillshare course on the index and match formulas. Before we jump into the content, let me take a second to introduce myself. I have been in the working world for almost seven years and all of that time has been spent in Excel for my time in college to my current role today, Excel has been a part of my daily toolkit. I've developed a passion for analytics and interests in figuring out the most efficient solutions and a love for automation. My goal for this video series is to make you an expert on the index and match formulas. The index and match formulas, when combined, provide you with a Lookup function that outperforms the VLookup and H look-ups and ensures your calculations will not change based on additional rows or columns, the index match can boost the effectiveness of ad hoc spreadsheets and complex cost models. Join me in this series as we unlock the next level in your cell potential and leave the other lookup functions in the dust. Let's get after it. 2. Index & Match Basics - Part 1: Exact Match: Hi everyone, Welcome to the first video in the series. In the upcoming videos, we will be covering the index and match formulas. The index formula has us define a range of cells and allows us to return the value of a cell from within that range given the cells coordinates. The match formula asks for a lookup value and the defined range, or the lookup value appears, the match formula will then return the column or row number in the defined range or the lookup value is found. While these formulas can be used on their own, the most common use case is when they are combined. Over the next few videos, we will work through some examples of the different ways these two formulas can be used. Let's dive in. In this example, we have a table of invoices for March that is missing the customer city. We would like to pull the customer city from the lookup table into the working table. We can use the combined index and match formulas to help us. The index formula starts by asking for the IRB ray that has our desired data, which is B3 through B6 in the lookup table. Note that this will be set as an absolute reference so that the range doesn't change as we copy the formula to other cells. Next, the index formula wants the row number in the array that we just defined of the cell where our desired data resides. Because the specific row number for the city that we want to return will change based on the customer. We will need the row number to be dynamic. For this, we will use the match formula. The match formula first asks for the lookup value a ten. Next, it asks for us to enter the lookup array, which will be A3 through a six. Just like the index array above, this will also be set to an absolute reference. Finally, it asks for the match type, which in this case will be 0 because we want an exact match less than and greater than matches will be explained in later videos. But know that those are typically just used for matching numbers. The index formula has a final optional input column number that we will not be using in this example, but we'll take a look at later on. Okay, we've finished the formula. Let's copy it down and see if we're getting the correct cities. Fantastic. Now, since this formula combination can be a bit confusing, let's quickly review what happened. First, we use the index formula to get the range of cells where the customer city information that we wanted was located. Since we didn't want to manually input the row number of the customer city for each invoice line, we use the match formula to dynamically return the row number. The match formula return the row number of the customer in the lookup table based on the lookup value that we provided from the working table, we were able to use the row number that the match formula provided as an input into the index formula to then return the customer city. Keep in mind that the index formulas or ray and the match formulas lookup array have to be the same row values. In our case, rows three through six, in order for the formulas to properly work together. Alright, let's move on to another example to solidify our understanding. 3. Index & Match Basics - Part 2: Less Than Match: Hi everyone, Welcome to the second video in the series. In this video, we're going to build on the combination of the index and match formulas and dive deeper into what the match formula can do. Let's get into it. In this example, we have a list of customers and the annual sales achieved by each. For each customer, we want to determine what their rebates should be based on the rebate tears associated with the annual sales targets in the lookup table. Like in the previous video, the index formula begins with the array of the desired data, B3 through B8. Next, in order to make the index formula is row number Input Dynamic, we have to use the match formula. In the previous video example, we use the customer as the lookup value and 0 an exact match as the match type. In this example, we're going to use a number, annual sales as the lookup value in one less than as the match type. Let's walk through this. The match type will be annual sales between the lookup array will be a three through eight, and the match type will be one. Let's quickly go through what's happening in the match formula. This time, the mattress first taking the annual sales lookup value and matching it to the annual sales in the lookup array. Since we entered less than as the match type, the match formula is going to find the closest value in the lookup array that is less than our lookup value. It will then return the row number of the closest less than match, and it will be fed into the index formula as the dynamic row number. We had already given the index formula, the array of data. And now with a dynamic row number from the match formula, it will return the contents of the cell in the dynamic row numbers position within the array. Let's copy the formula down to see if we got the results we expected. Awesome, everything looks good. Let's move forward to the next example. 4. Index & Match Basics - Part 3: Index Match Match: Hi everyone, Welcome to the third video in the series. In this video, we're going to add an additional layer to the combination of the index and match formulas and additional match formula. In the last two examples, we looked at returning just the row number two, the index formula to get the desired data. In this example, we're going to use to match formulas to return both the row and column numbers. This will allow us to return a wider range of data with just a bit more work upfront. Here we go. In this example, we have a table of invoices for March that is missing the customers, city, state, and zip code. We'll use the information from the lookup table to populate our working table. Unlike the other two example videos, we will start with an index array that includes rows and columns, B3 through D6. The index array will have both rows and columns locked. One important note here, throughout this formula, it will be very important to pay attention to the reference types of the cells. Some cells will have the rows and columns locked in some we'll only have either rows or columns locked. If the wrong cell reference type is used, the formula will not return what we intended. Next, we will use the first match formula to dynamically return the row number. The first match formulas lookup value will be the customer a ten. This lookup value will have the column a locked, but the row ten open, the lookup array will be A3 through A6 with both rows and columns locked. Finally, the match type will be 0 because we want an exact match. For the second match formula to return the column number. The lookup value will be the column header city. In cell C9. This cell will have the row nine locked, but the columns C open. The lookup array will be B2 through D2, with both the rows and columns locked. Lastly, the match type will again be 0 because we want an exact match. Let's copy this formula to the rest of the cells in the city column and to the state and zip code columns as well. Sweet, everything is working as expected. Let's back up a bit and talk through how this formula is working. We began the index match formula by stating the range of cells containing the data that we want to eventually return. A quick note, we want to be sure that this range of cells only includes data that we want to be returned and does not include lookup values that we will use to get the data. Okay? Now that we have the range of cells containing our return data to find, the index formula now needs both the row number and the column number of where the data is stored. Instead of giving static numbers like row one and column two to return Ohio, we're going to use match formulas to dynamically return these values. We don't have to enter the coordinates manually. The first match formula is going to dynamically return the row number. We give the formula, the lookup value, the customer in the range of cells where the customer lookup value is contained. And it returns the row of the customer lookup value. Since each customer in the lookup table has corresponding city, state, and zip code information. The data that we want, returning the row number of where the match formula found. The customer also gives us the row number of the data that we want to return. The second match formula is going to dynamically return the column number. We give the formula, the lookup value, the column header, and the range of cells where the column header corresponds to. And it will return the location of the column header as a number given both coordinates from the two match values, the index formula is now able to return the value of the cell that we were after. That was quite a bit of information to absorb, but you powered through it. Since this is the last video in the series, I'd like to thank you for watching. If you like this course, please take a minute to check out my other courses. And as always, if you're feeling stuck, feel free to stop back and ask a question. Thank you.