Now let's create a second area here. So I'm going to go ahead and I'm just going to do control copy, and I'm going to do control paste here. We might want to create a reverse lookup where now we have student ID number and we have the name. So I'm going to actually delete all this and the student ID number. Let's just take Charlie's, control copy and we're going to put that here. Now what I want to do is I want to be able to look through the table here for student ID number and then it's going to provide the name, and then it's going to provide the assignment scores. Now you cannot do this easily with VLookup. Remember, VLookup will always look through the leftmost column of the second argument. For student ID number, there's no way I can output a column that's on the left of student ID because It's looking up student ID number in column B and the names are left of that. Instead of using VLookup, you can use these two functions known as Match and Index. So first, let me show you how the Match function works. I'm just going to put it in this cell here. If I type in Match, we can look up a value, so I'm going to look up student ID. The second argument is a lookup array. So I'm searching for student ID number in this array, there. You see we have that structured reference there because it's a table. The third argument, it's really important again, if you're trying to get an exact match to type in a zero here. For the match function, zero means exact match. I'm going to press "Enter". What this means is that it's finding 276544776, that's student ID number in Row 3 of that array that I put in as the second argument. Now, if we take that, we can use the index function. So if I know that the student ID numbers in the third row down, then I can use the index function. The first argument of the index function is the array that you want to output. Again, that's our Name column from our data array. That's a structured reference. Then I can use that row number that I calculated it using the Match function and I can just put that in as the second argument and then it's outputting Charlie. So what I'm going to do is in this Name box, I'm going to join both of these into one single formula. This formula in E11 is using the formula in E10. So I'm just going to go in here, I'm going to copy that, press "Enter" and inside Charlie, instead of E10, I'm going to put that Match function. So I don't even really need E10 anymore. Now I can take this, I can do control cut, and go up here to H10, then I can paste that in there. So now it's automatically updating. If I take one of these other student ID numbers, so maybe Chester's. Control copy, I paste it in here. It automatically updates. Now, I can do the same thing. Instead of name, I can do it with homework, Homework 2, and Exam 1. By the way, you could also do these with VLookup. But let me show you how we can do this with Match. The lookup value is the student ID number. I'm going to make that an absolute reference. The lookup array, I'm looking again through student ID and I want an exact match. So I put a zero. This is for Chester, so this will be the 1, 2, 3, 4, 5, 6, so this will return six. But then inside that, we need to use the Index. The array that we want to use for Homework 1 is this vector. So I can press comma, just put a parentheses at the very end of this, and I press "Enter" and it's saying that Chester's score on Homework 1 was a seven. I can drag this down, but I need to modify a few things. For Homework 2 instead of the Homework 1 column of our data table, I can just modify this to be Homework 2. I can press "Enter". Here instead of Homework 1, this is just going to be Exam 1. There we have it. So now we can change the student ID. Let's go ahead and copy Rodney's just to make sure this is working. So it's looking up the name of that student, the Homework 1 score of a nine, Homework 2 score of six, and Exam 1 score of 87. So there's two ways to do lookups. You can use the VLookup if you're looking up something that's in the leftmost column of table, or if it's something in the middle and you're trying to look for something on the left of that column, you can use the Index, Match combination. There are other lookup functions. There's HLookup for horizontal lookup. There's also just a fairly old lookup function, but the VLookup or combination of Index Match work quite well for most things in Excel. By the way, let's just do one more enhancement to this file here. I'm going to add in some Data Validation. So cell H3, let's go up here to the Data tab, Data Validation. This is the name. So I'm going to just make this a list and the source is going to be our names over here. Click "Okay". So that allows the user to select names from the drop-down menu, which is just a little more convenient. Also here, the student ID number, we can go ahead and add the input validation for this. This is going to be a list and same thing, our source are the student ID numbers over here. Then we can click "Okay". So that quickly enables one to select a student ID number from the drop-down list.
