VLOOKUP with MATCH for Dynamic Column Indexing

VLOOKUP and the 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.

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. 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 who 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. At this point in the class I usually ask students what is the, well first let's type MATCH. I ask students what is the lookup value for the MATCH function and usually there's 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. 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 parentheses. 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 parentheses. I'll press ENTER and I get my answer. Then we give students an opportunity to do them to do this themselves.

I'll look up the product name for order ID 13294. That's simply going to be equal to VLTAB. The order ID is the lookup value for the VLOOKUP.

I'll enter a comma. The table array is below. I'll use CTRL shift right, CTRL shift down.

Then I'll enter a comma. Now I'll 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 parentheses.

Comma zero. Close parentheses. Press ENTER and I pick up the product name.

We do one more exercise. If I head over to the sheet called database and if in this situation we're looking to pick up the customer and sales rep for order IDs 13295,13299, 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 CTRL 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. CTRL shift right arrow 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. Okay so now I'm 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 values 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 O-R-D-E-R-S.

Then you can enter a comma and then type in M-A-T-C-H 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. Now here is where we can use mixed referencing. I want this VLOOKUP to work throughout all the cells I have selected.

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.

One, two, three. 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. One, two, and there we go. If I did this correctly, if I press CTRL-ENTER, the answer should fill all the cells around that I have selected.

All around the original cell. I'll press CTRL-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. Won'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 the 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. 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 on in our Level 3 class.

That is VLOOKUP with the match function.