VLOOKUP is an extremely useful formula in Excel. Unfortunately – for the SEM beginner – it is also one of the most confusing when you are just starting out.
Since I’m a relative beginner in paid search, the brunt of my work is production tasks. VLOOKUP is something that I use every single day. So you could imagine my frustrations the first few weeks of work, when I had no idea how to use it.
Of course I asked for help, but learning VLOOKUP from someone who already knew it and its intricacies proved to be not so helpful. I desperately wanted someone to just lay it out in the plainest, most stripped-down way possible. So that’s what I will do for you here: I’ll walk you through the framework steps that I wish I had known.
(Disclaimer: VLOOKUP is a very helpful and diverse formula. I don’t even know everything it can do yet.)
What is VLOOKUP?
According to Excel’s formula description, VLOOKUP “looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify.” Super helpful, right?
To dumb it down for you, VLOOKUP lets you pull information about your selected cells into your current sheet, from other sheets or workbooks where that value exists.
Let’s say you have a spreadsheet of select keywords from an account with no data – We’ll call this Sheet 1 – but you would like to know what the Avg. CPC for each keyword is. You have another sheet that is a keyword report with all the data for every keyword in the account – this will be called Keyword Sheet. You can avoid manually sifting through all of those keywords and having to copy and paste the Avg. CPCs by using VLOOKUP. VLOOKUP will automatically fill in the Avg. CPCs on Sheet 1, from the data on the Keyword Sheet.
This will start making more sense as I break down the formula.
The formula for VLOOKUP looks like this:
This is the formula color-coded:
As you can see, it is made up of four different parts, separated by commas. Knowing what each of these parts represents was a big struggle for me at first, so I’m going to break them down for you and tell you how to use them.
The lookup value portion of the formula is what you want to look up somewhere else. It is what you want information about.
Sticking with our previous example, here is my Sheet 1 keywords, with no data. I will be using VLOOKUP to find the CPCs for these keywords. If I want to know the CPC for the keyword “maple products,” then the cell containing “maple products,” is my lookup_value. (Editor’s note: Charlotte works in our awesome Vermont office.) You can see this highlighted in red below.
At this point, your formula should look like this: =VLOOKUP(C2,
Now it is time to fill in the table_array.
The table_array is the array of cells you want to search within for your lookup value. So here I would want to look for the keywords on Sheet 1, within the Keyword Sheet with all the data.
To select your table array, once you have selected your lookup value, type a comma to signify that you are moving on to a new part of the formula. Then you simply can highlight, or drag through whichever portion of data you would like to retrieve data from.
Just a note about table array: If you are searching for your lookup value in the keywords tab, your table array must start with the keywords column. The column in which you are looking must always be the first column in the array. So if it’s in the middle of your sheet, you should start your table array in the middle too.
In other words, start your table array with whatever column corresponds with your lookup value, and continue it to the column with the data that you are looking for.
In this example, I am looking in the Keyword sheet. I will start with the Keyword column and continue to the Avg. CPC column. You can see this in the example, highlighted in blue:
Another note on table array: it is important that after you select your table array, and before you type your next comma, that you press the F4 key on your keyboard. This will insert little dollar signs in your formula, which ensure that the array you’ve selected stays aligned. If you plan on filling your formula to apply to the entire data set, this is essential for proper results.
It took me a while to figure this part out, so even if you don’t know why…just press F4 after you select the table array.
So at this point your formula should be looking like this: =VLOOKUP(C2,’Keyword Sheet’!C2:$H$10,
You’ve got your lookup_value and your table array; it’s time to move on to the next part.
COL INDEX NUM
The col_index_num, is simply the column of data that you would like to be reflected in your returned value.
In our example, we are trying to return the Avg. CPCs from the Keyword Sheet to our Sheet 1, so the col_index_num, would be the column that contains this data, or the Avg. CPC column on the Keyword sheet.
The trick here is that when filling in the formula, this part is typed in as a manual number. The number you want to type in for col_index_num is the number of the column relative to where the table_array started.
So in the example, the table_array started with the keyword column, or column C. Therefore that col_index_num is 1. That means that the Avg. CPC col_index_num would be 6 – because it is 5 columns away from the Keyword column. If I type in “6” as the value for the col_index_num, the data from the Avg. CPC column will return to my original sheet once I have completed the formula.
Here is what that would look like.
Now we’ve got three of the four parts of the VLOOKUP formula filled in: =VLOOKUP(C2,’Keyword Sheet’!$C$2:$H$10,6,
On to the last part of the formula.
range_lookup has two possible values: TRUE or FALSE. By typing in TRUE, you are allowing values to be returned for approximate matches. So far, I have never used this option. This would be a poor choice to use here, because so many of the keywords contain the term “maple.” In my experience, I always type in FALSE. This assures that data will only be returned for the exact value of my lookup_value.
Once you type in FALSE, you can add your last parenthesis at the end of the formula, and hit Enter.
This should fill in the value you’re looking for. Here is what that will look like:
Your completed formula will look like this: =VLOOKUP(C2,’Keyword Sheet’!$C$2:$H$10,6,FALSE)
Now, to finish you can just auto-fill the rest of the data by double-clicking on the corner of the cell containing the formula. This will return the Avg. CPCs for all of your selected keywords in Sheet 1.
Voila! The missing data has been filled in.
Again, this is a very bare-bones explanation of VLOOKUP, but it is one that I would have found very helpful during my first couple of days on the job.
Check out this short video of VLOOKUP in action, so that you can get a more fluid picture of how these steps go together.
Again, VLOOKUP is an extremely diverse formula that can be used in many different ways for many different purposes. I certainly don’t know all that it is capable of, or even all the ways in which it can be used. What I aimed to do here was to provide a bare-bones, simple explanation of exactly how to use it for SEM and Excel beginners alike. Hopefully those of you out there struggling will find this as useful as I would’ve during my first week. Good luck!