Excel VLOOKUP and MATCH Function

Lookup functions are covered extensively in our NYC Excel classes. For those outside New York, find and compare the best Excel classes near you or online Excel classes.

VLOOKUP and MATCH Function 

We can create a more accurate VLOOKUP by enhancing the determination of the column index number by using the MATCH function. Instead of counting columns to determine the column index number, the MATCH function can do this. So when we get to the column index number, we're going to substitute the match formula for the column index number in the VLOOKUP function to more accurately find the column index number. 

Exercise 1 

So I'm going to start with customer. This is usually the exercise that I perform first when I'm teaching the class. I start out by reminding students that you should already know VLOOKUP from Level 2. So I'm just going to go about doing a regular old VLOOKUP. 

I'll type in equal, the minimum I need to type in terms of letters are "VL," and then I can press tab. Now the LOOKUP value is the value that we're going to use to look up the information for the customer. So that's going to be the Order ID. Then I'll enter a comma, and then I need to select the table of information that contains the information I need, making sure that the first column contains the LOOKUP value. 

Now I'll enter a comma, and this is where I'll get to substitute the MATCH function. First, let's type MATCH. I ask students, what is the LOOKUP value for the MATCH function? And usually, there is some hesitation here. Ultimately, people, or someone, confidently answers "Order ID." And this is where that would be the wrong answer. It's almost as if people forgot how to do a VLOOKUP, because when we get to this part of the VLOOKUP, we're looking for the column index number for customer, not the order ID. 

So for the MATCH function, we need to select customer for the LOOKUP value, then we can enter a comma, and there's one row or one column where we'll find customer. That one row for this table has a special name. It's called "the headers." 

So now I usually tell students when we get to this section, "You can think of 007." Why? Because to complete this VLOOKUP function with the match, we're going to enter two zeros at the end. I'll enter a comma. First zero is an exact match for the MATCH function. Close parenthesis. Then I'll enter a comma and the second zero is the zero for the VLOOKUP, and that tells me that I want an exact match. Zero is also a substitute for false. Close parenthesis, I'll press Enter, and I get my answer. 

Then we give students an opportunity to do this themselves. I'll look up the product name for Order ID 13 to 94. That's simply going to be equal to "VL," Tab, the order ID is a LOOKUP value for the VLOOKUP. I'll enter a comma. The table array is below. I'll use Control Shift right, Control Shift down then I'll enter a comma. Now I use the MATCH function. MATCH, the LOOKUP value is product name, and I'm going to look for product name across the headers so I can find the column position. Then double O's, zero here, close parenthesis, comma, zero, close parenthesis, press Enter, and I pick up the product name. 

Exercise 2 

We do one more exercise. If I head over to the sheet called Database, and in this situation we're looking to pick up the customer and sales rep for order IDs 13 to 95, 13 to 99 and 13300. I could select the entire range. Before I do, though, what I want to do is name the table and the headers to make it easier to write my VLOOKUP. So I'll press Control A, I'll head over to the name box, and I'm going to call this "orders." Then I'm going to select the headers. Control Shift, right arrows is what I use to select the headers there. Go up to the name box, and I'll call this headers. I'm keeping it nice and simple. 

1. So now I am going to go over here and select the entire grid. I'll do a VLOOKUP and I usually keep it a secret what I'm going to do right now. I'll type in equal, VL, then I'll press Tab. The LOOKUP value's to the left. I enter a comma. This is what naming the table does for you when you get to Table Array. Makes it very simple for you to simply type "orders," then you can enter a comma and then type in "match" because now we're looking for the column index number, and that is going to be customer. 

This is also what's going to be really simple. I named my row "headers," so I'll just type "headers," zero to get an exact match for the MATCH function, and then zero to get an exact match for the VLOOKUP. 

Mixed Referencing 

Now here's where we can use mixed referencing. I want this VLOOKUP to work throughout all the cells I have selected. So when it comes to C12, I should lock either the column or the row. Now how I determine which is by looking at the information, and it's in a column. 

So what I'm going to do is press F4 three times. That locks the column. Now for D11, this is the value that I want to look up in the row. So I'm going to press F4 twice, and there we go. If I did this correctly, if I press Control Enter, the answer should fill all the cells around the original cell. I'll press Control Enter, and there we go. 

If I click the plus sign next to row 23, I reveal maybe more Order IDs that I need to find customers and sales reps for, so I'll simply use the autofill handle. Don't go too far. Let me just go down right to the last value. 

Then I'm going to head over to sales rep. And this is just to show you, that a mixed reference formula is still going to work. I'll copy product name. Maybe I'm also interested in product name. I'll go here. And then I'll select the entire range and autofill to the right and see if that still works. And it does. 

Recap 

So in this section, we saw how you could use the MATCH function to make a more effective VLOOKUP and look up multiple values and only have to write the VLOOKUP once if you use mixed referencing, a topic we covered earlier in our Level 3 class. That is VLOOKUP with the MATCH function.