[MUSIC] As discussed in the introduction, INDEX MATCH can be used to look up information in a grid with both an X and Y variable. We will utilize the optional column reference which we have previously defaulted to one in prior exercises. Let's look at our new data set to get better understanding of why this would be useful. Our data set has names across the top, and personal identification going down. This would mean the names would be an X input or column input, and the personal identifiers would be the Y input or row input. In this exercise, we are looking for personal information about Rod. Why don't we first look up where in the data set the information for Rod is. We're going to start with the MATCH formula. So let's do =MATCH, followed by the lookup value. Here it is, his name. We are going to lock this cell, because it's the same for all the attributes. The lookup array is a list of names across the top in our data set. You'll see that the name range inserted itself. And we will use a match type of 0, as we're looking for an exact match. If we hit Enter, we find out that Rod's information is in the fourth column of the data set. And now we can copy and paste our formula down the remaining attributes, since Rod will be in the fourth column for all answers. Now we're going to use the MATCH formula to look up where the eye color is, in the personal attribute field. We are going to search for eye color using MATCH. Lookup value is going to be eye color, and our data set is going to be the personal information. The name range pops up, and the MATCH type will be exact. Ensure to lock the rows and columns where required. I find that many formula issues are a result of missed cell locking. If we copy this down for each cell, it's telling us what position, weight, height, sex, and eye color are located in. We can combine both the x and y MATCH in an index formula to find the corresponding x and y coordinates for Rod's personal information. We will do =index, and this time we select the entire data set. We need to select the entire data set since, we're going to utilize both the row and column lookup, and need the entire range of outcomes to work with. Let's start at B38 and select down to I44. I'm going to lock the data we just selected as we've done throughout this exercise, so it does not shift on me when I copy it. Now I am looking at the row number. We already looked up the row number for eye color in D147, so let's input that here. Similarly, the column number can be found in C147. If I close the formula, hit Enter, we get brown. Now, if I copy this down, we now have Rod's eye color, sex, height, and weight. We just created a look up formula utilizing two variables, however used three formulas. Just like before, we do not need to do this in two separate steps. We can do this all at once by replacing the row and column inputs with the match formula that we wrote in columns C and D. Let's start by typing =index, and select our data set, making sure to lock it. For the row number, we are going to use the match formula we were working with before. The lookup value is going to be eye color that is saved within the personal info, and the match type is exact. We close the first match with the parenthesis, and then hit comma. Notice that Excel is now asking us for the column number. We are going to do the same thing we did before to look up Rod's column position. We will use MATCH again, but this time, we are looking for Rod. This reference is anchored, so we're going to lock both the row number and column so it doesn't shift on us. The lookup array is name, and the match is exact. We close the second match, and then we close the index and hit Enter. We have an eye color of brown, which matches the answer we got when we did it in two parts. If we copy this down, we now have all of Rod- picking up both the x and y variable. We just built a two variable lookup in one single formula. Now it's your turn to try in problem seven. Use an INDEX MATCH MATCH to write a single formula that will pick up not only the personal attribute, but also the person's name, and find the matching information using the same data set and formula. Good luck. [MUSIC]
