In part 1 of this series we took a look at VLOOKUP formula basics. If you want to learn how a VLOOKUP formula works, or know someone else who is struggling with VLOOKUPs, check out the first article “Discover a simple way to understand how VLOOKUP formulas work in Excel“, and come back to this article.
Here in part 2, we have a 14 minute video that explains three different ways to write the VLOOKUP formula for exact matches. If you have never used the COLUMNS function or MATCH function with VLOOKUP, you are in for a treat because you will find out how to turn the standard VLOOKUP into a dynamic formula. As usual I have included a sample workbook with a table of fictional employee data for you to download and follow along with the examples.
Download the VLOOKUP Employee Table Sample Workbook
In order to follow along with the video and article please download the Sample Workbook “VLOOKUP_Employee_Table.xlsx” by clicking here
Note: the sample workbook has been tested on Excel 2010 (32-bit, English language version) and you may get different results in other version of Excel. If you find that you can’t use this workbook on your version of Excel, please let us know using the comment section below.
The sample download workbook contains slightly different cell references compared to the workbook used in the video, but the data itself is identical and provides a convenient way for you to practise your VLOOKUP formulas.
VLOOKUP Tutorial Video Part 2
Watch our 14 minute video to help you understand three different ways of writing the VLOOKUP formula for exact matches. Click on the video below to play it and listen to my audio commentary.
Note: The video was transcribed by SpeechPad.com and then edited into the form you see below.
We’re going to have a look at three different ways to write VLOOKUP formulas. One is a straightforward, simple approach, the second is using the columns function, and the third is using the MATCH function.
Here we have an employee LOOKUP table with the employee ID on the left. You see we’ve got employee full name, SSN, department, start date, and earnings for each of these employees.
What we’re going to do is use a VLOOKUP formula to populate this top table, so that whenever we change the data validation drop-down, say we change it from 3 to 2, we shall see the corresponding line pulled out at the top.
Note about the Data
None of the names here are real people, they’re all computer-generated names. So if you see someone’s name that you recognize, it isn’t that person.
Learn more about Data Validation
By the way, if you want to learn more about data validation, you should check out my book “Power Tips for Excel.” That takes you through some data validation tips and tricks.
What we will cover in this video
Now I’m going to take you through three different ways of writing the VLOOKUP formula.
- Simple way – type in numbers for column index number
- COLUMN function – use the COLUMN function to specify column index number
- MATCH function – use the MATCH function to specify column index number
The first is the standard approach, the second uses the COLUMN function, and the third uses the MATCH function. While the first way is the simple way, it is actually not the most efficient in this case. We’ll go through method one, then we’ll check out the column method and the match method which in my opinion are usually more useful.
VLOOKUP Method One – Type in numbers for column index number (not dynamic)
The simple method is to use the VLOOKUP formula without any other functions. Go to cell C5 and we will set up the VLOOKUP to pull up “Full Name” using the employee ID. In the formula bar, type =VLOOKUP, then type out the full VLOOKUP formula. When I click on E5 I also press the F4 key (three times) to lock this to absolute references so it doesn’t move from column B ($B5).
The table array is down here, but I’m not going to select the column headings. I will just select the data. Again I press the F4 key once to lock it to absolute reference ($B$10:$G$59).
Next I enter the column index, which is 2 for “Full Name”. Finally I type False, to specify that I want an exact match.
Press the Enter key, and you’ll notice that employee EMP002 gets pulled up, Lucian Franklin. And what I should be able to do now is copy that across into each of these cells (D7:G7). What I’ll do is go Control + C for copy, and Control + Alt + V for paste special because I don’t want to overwrite the formatting, so I’m going to select paste formulas.
Now, you see it’s just put the full name in each of these boxes, which we don’t want.
Instead, I need to go up here, and change the column index number from a 2 to a 3. Press tab, and then for Department, I have to change it from a 2 to a 4. Press tab again to move right. And then in Start Date, I need that to be a 5. Press tab. And in Earnings, I need that to be a 6. (See screenshot below)
So now we have the LOOKUP table completed using the simple approach. But it does mean that you have to manually enter 2, 3, 4, 5, and 6 as the column offset because when you copy and paste the VLOOKUP formula the col_index_num does not automatically update for you.
VLOOKUP Method Two – Use the COLUMN function to specify column index number (semi-dynamic)
Method two uses the columns function, which you can see here. So the formula is going to end up like this, it’s going to be a VLOOKUP with the standard parameters. But it’s also going to use the function COLUMNS with an array, and I explain that later, with a FALSE at the end for an exact match.
Go to cell C5 and type =VLOOKUP(. Select cell B5 as the lookup_value, and press F4 to lock the cell reference to $B$5. That means when it is copied across, it will always reference cell B5.
For the table_array, I will select the data excluding the column headings (as before, this is good practice when writing VLOOKUP formulas). Press Control + Shift + Right arrow, Control + Shift + Down arrow, and that will select all the data in the table (this works because there are no empty data cells). Press the F4 key to lock that to absolute references.
And now, for the column index number, for “Full Name”, I want it to come out with a 2. And what the function COLUMNS does is it counts the number of columns in a particular array, where an array is just a group of cells on the worksheet.
We want the array to start at B9 and go up to C9. I’m going to lock the range reference to the column B, by inserting a dollar sign ($B9:C9). And so when I copy this across, you’ll that the first cell in the range reference stays in column B while the second cell in the range reference moves from column C to column D to column E etc. depending on how far I copy across (e.g. $B9:E9)
The last argument in the VLOOKUP formula is the range_lookup, and I set that to FALSE for an exact match.
So for employee EMP004 it has found “Denton Q Dale”. And now I’m going to copy the VLOOKUP formula, using Control + Alt + V to copy and paste special formulas only, so I don’t overwrite the existing cell formats.
Now to explain what happened. The COLUMNS function looks at the range of cells you give it, and counts the number of columns in that range. As you copy the formula across, you notice that the range stays fixed on column B, so it stays at $B9, but as I copy it across the second part of the range changes to C9, then D9, then E9, then F9 and finally G9 depending on how far to the right I copy.
So in cell G5, the COLUMNS function looks at the range $B9 to G9, and counts how many columns are there. So that would be one column, two columns, three columns, four columns, five columns, six columns, and when it counts six columns, it will give the VLOOKUP formula, the right parameter for column index number.
VLOOKUP Method Three – Use the MATCH function to specify column index number (Dynamic)
Method three uses the MATCH function instead of the columns function to give you the column index number. The MATCH function returns the relative position of an item in an array that matches a specified value.
So for example, if I want to find out where “Full Name” was in this array, what I do is type “=MATCH(“. First function argument is lookup_value so I select C4 (“Full Name”. The second function argument is lookup_array so I select B9:G9 (the data table headings). The third and last function argument is match_type and we want an exact match so type 0 or FALSE.
The formula above gives a value of 2, which is correct, because the first column (“Employee ID”) does not contain “Full Name”, whereas the second column does contain “Full Name”.
I will modify the MATCH formula to make the range B9:G9 into absolute references by selecting it and pressing F4. The reason is that we want to copy this across and have it update with the correct range, so copy the formula using CONTROL + C, then paste it to the cells on the right, pressing CONTROL + ALT + V to paste special as formulas.
When the formula is copied across to the right, you can see the results are 2, 3, 4, 5 and 6. What it’s done is evaluates the correct column number to use in our lookup formula by matching the contents of row 4 to the contents of row 9.
Time to combine the MATCH function and VLOOKUP function
Now we now work through the VLOOKUP formula to see why the MATCH function can be very useful.
This is the combined VLOOKUP + MATCH formula: “=VLOOKUP($B$5, $B$10:$G$59, MATCH(C$4, $B$9:$G$9, 0), FALSE)”
For the third function argument column_index, we use the MATCH function to lookup up the value “Full Name” in cell C4, and find its position in the range of column headings in B9:G9.
Note the use of absolute references to lock the first argument of MATCH lookup_value to row 4, and lock the second argument of MATCH lookup_array to B9:G9. Set the match_type to 0 for exact match.
Copy the VLOOKUP + MATCH formula from cell C5 using the keyboard combination CONTROL + C, then paste special as formulas into cells D5:G5 using the keyboard combination CONTROL + ALT + V for the paste special dialog box.
This is the result:
You will see that by writing one VLOOKUP + MATCH formula and copying it across, you did not need to manually change the col_index_num inside the VLOOKUP formula. And if you were to change the value to cell C4 from “Full Name” to “Department”, the VLOOKUP formula in cell C5 dynamically knows to look up the “Department” instead of the “Full Name”. This demonstrates the flexibility of the VLOOKUP + MATCH combination.
What could go wrong with Dynamic VLOOKUPs?
Here’s a brief list of things that could go wrong with your dynamic VLOOKUP formulas. It’s by no means a comprehensive list, but covers a few fundamental errors:
- Using COLUMNS function (Method 2) when the order of your lookup table headers does not match the order of your data table headers (e.g. “Full Name, SSN, Department, Start Date” vs. “Full Name, Department, SSN, Start Date”)
- When using the MATCH function (Method 3) your lookup table headers do not match your data table headers (e.g. misspelling “Depatment” instead of “Department”)
- Your absolute references might not be correct, and as you copy your dynamic VLOOKUP formula into other cells the COLUMNS() or MATCH() function could be looking up the wrong cells.
Time for you to practise your VLOOKUP formulas
If you haven’t already downloaded the sample workbook, you can do so by clicking here.
I recommend you go to Excel and play around with VLOOKUP functions and try to insert COLUMN and MATCH to make the VLOOKUP formulas dynamic, because that way, you’ll master them quicker. All right? Have fun!
If you found the video and article helpful share it with three or more of your colleagues and friends using the sharing buttons on this page, or email this link directly to them http://www.launchexcel.com/dynamic-vlookup-formula-examples/