Use the MATCH() and INDEX() functions

- [Narrator] You may recall that in an earlier video we were using V-lookup and H-lookup to find our desired value. And as long as the lookup value is in the first column or the first row of your data, those two will work just fine. However, a lot of times our data is not laid out in that manner, and if that's the case then match and index are the functions that you'll want to try and use. We will use match to find the row number on which our value resides. Once we have completed that match function, we will cut the function and actually paste it right into the row value in the index function. The index function will then return the value that it finds on that particular row that was found by the match function and it will base it on the column number or the array that we enter into the formula. So we'll be creating match and then nesting it right inside of index. Sounds like a lot of steps, but it's really easy. Let me show you how. This is our match index workbook, and we are focusing on our employee information, and over in columns J and K we'll specify an employee, and notice I have set up a data validation list for us to use. And we'll find the salary amount for the employee that we have selected from the list. I want you to look at how this data is structured. Column A contains the department, and column B has our employee name. The information we're looking for is column H. The information is kind of spread out throughout this data and that's why match and index will be the perfect match. In K3 I'll begin be creating my match function. Equals match. Tab to step into it and you can see the syntax underneath. We're looking first for the lookup value, then the array, and then the match type. Well, let's go ahead and go into our function arguments window. It's a little easier to work with formulas in this arguments window because it takes you step by step through the process. So, our lookup value is J3. The lookup array, in other words, where is Excel going to find this employee's name? Well, it will be in column B. I'll click in B4, use control, shift, and down arrow on my keyboard to select the range. We can go ahead and make it an absolute reference by pressing F4 on the top row of our keyboard. I'll tab down to the match type because I want it to be an exact match, I really want it to find Adams, David. I'm going to use a zero. That'll give me an exact match. Now look at the formula result in the lower left-hand corner. The result for the salary for David Adams will be on row 376. Now it's not the same numbering as we see here one, two, three. But row one begins on the first row of my array, which in this case is on row four. So let's go ahead and click OK, and there's our row number. Now we will take the match function and I'm going to use control and X on my keyboard to cut that, so I can paste it into the row reference in the index function. We'll start again in K3 and type equals index. Tab to step into it. And notice you have two different options here. You can use an array, which is what we'll do. We'll select the entire salary range or you can just use a reference to the row and the column number and the area number. When I go into my function arguments window, this is where I can again make the choice of leave array selected and click OK. The array will be the information in column H. Now I could select all of the data from A4 all the way over to H4 and then select all of that information downward, but I'm just going to use the salary column. I'll make it a little simpler. Control, shift, and down arrow will select that entire range, and F4 will make it an absolute reference. Th next is what's the row number. Well, we use match to find that row number. So if I control+V to paste that function in, remove the equal sign. Notice on the right-hand side of this field there's my row 376. I'm going to tab down to the column number. We only selected one column. I don't need to put anything in here. We'll just simply click OK. Let's format that as currency. And the salary for David Adams is $49,260.00. Well, let's find his record and just verify that. Control+F for find. Adams. And here he is and yes indeed his salary is $49,260.00. So that's how you can use the match function to find the row on which your desired value resides and then nest that right into an index function so that you can complete the function and find exactly the information you're looking for.