If you read our first two blogs covering Excel, you should know the basic tips and tricks and how to clean up a spreadsheet to make it easier to analyze and look nicer if you share. Now it’s time to get into some more advanced Excel skills: Formulas!
Of course, a pay-per-click (PPC) spreadsheet wouldn’t be complete without the use of formulas. Some of the more popular ones for PPC use are the SUM, AVERAGE, and AVERAGEIF formulas.
However, one of the more powerful formulas is the VLOOKUP. The VLOOKUP is great when you’re working with multiple sheets of data. It works by taking a value that is found on multiple sheets and using it as a reference to import corresponding data from a different sheet. One situation where I use it all the time is when looking at Google Ads shopping reports, and I want to look at individual product performance of Shopping campaigns.
For whatever reason, Google Ads doesn’t allow you to include metrics like “Conversions” and “Conversion Value” in the same report as “Product Title.” It makes no sense to me why this data isn’t available, but that’s for another day. Nevertheless, there is a workaround, thanks to the VLOOKUP.
As I explained earlier, the VLOOKUP will use a value found in multiple sheets as a reference to bridge data together. Sticking with our current example of analyzing Google Ads shopping report, that magic value is “Item ID.” While Google won’t allow you to view conversion data based on a product’s title, it does allow you to see the product’s Item ID.
For those unfamiliar with what an Item ID is, it’s a backend attribute used to uniquely identify each of your products. Typically, it’s much easier to look at a Product Title and understand what the actual product is versus looking at an Item ID.
So, what you can do is download one report with the “Item ID” and all of the other metrics you want in your report, including conversion data: conversions, conversion value, etc., and then download a second report that contains just the “Item ID” and the corresponding “Product Title”.
Then, in your first sheet with all of your metrics, you can use a VLOOKUP formula to add in the matching Product Title with the conversion data. And voila, you have a much more digestible look at how your products are performing.
How you actually create the VLOOKUP formula is fairly simple when you break it down. It’s made up of four parts:
- Lookup Value, or lookup_value
- Table Array, or table_array
- Column Index Number, or column_index_num
- Range Lookup, or range_lookup
Now that we know what a VLOOKUP formula consists of, we can see what it actually looks like when entering it into our formula bar. As with all Excel formulas, it starts with an equal sign followed by the name of the formula. Then it has an open parentheses, followed by each of the individual components of the formula separated by commas. So without the actual cells, it looks like this:
=VLOOKUP(lookup_value,table_array,column_index_num,range_lookup)
But what are these individual components, and what role do they play in the VLOOKUP formula?
Lookup Value
The lookup_value essentially acts as a key or a legend on a map. It’s the reference cell or the value found on both sheets of data, and it’s something that we already know. The formula uses it as a reference to match it with the other correlated data you’re actually looking for. In our example, the lookup_value would be the Item ID since it will be present in both of the product reports.
Table Array
Table_array is the full range of data that you are pulling information from. The table_array will contain both the lookup_value and the other correlated data we are looking for. In our example, the lookup_value is the Item ID, and the returning correlated data would be the Product Title. The VLOOKUP formula will search through the table_array looking for the lookup_value (Item ID). Once it finds the lookup_value, it will then return the data affiliated with the lookup_value (Product Title).
Oftentimes, when doing a VLOOKUP formula, you will be dealing with two separate sheets of data. I’ve found that both sheets should be in the same Excel file for this to work best. When you are in the middle of filling out the formula, you can freely switch between sheets before selecting your Table Array, and Excel will automatically populate the new sheet within the formula for you.
It’s also important to note that when using a VLOOKUP formula, especially for our example, the formula will be copied and pasted for all the rows below. In order to make sure the table_array isn’t changing as it’s copied and pasted to the rows below, it’s best to put the “$” signifier before both the beginning cell’s row and column and final cell’s row and column of the table. This is what’s known as an absolute reference.
For example, say our table_array is A2:E100. To make sure when we copy and paste the table into the row below so it doesn’t turn into A3:E101, we qualify the table like so $A$2:$E$100.
Column Index Number
The table_array is composed of multiple rows and columns of data. The VLOOKUP formula works in that it will find the lookup_value, and all of the correlating data will then be within the same row as the lookup_value. However, whatever specific piece of correlated data you choose to return will be based on the column_index_number.
For example, when I’m using a VLOOKUP formula to match the Product Title to its Item ID, my table_array usually consists of just those two columns of information. So the column_index_number would be 2 (the first column being the “Item ID” and the second column being the “Product Title”).
Following along with the VLOOKUP formula function, we are telling it to take our lookup_value and search for it in the table_array. Once it finds the row containing the lookup_value, we are telling it to return the data in column 2 of that particular row.
Range Lookup
The last piece of the formula determines if you want the lookup_value to match exactly or only approximately. This is determined by either a TRUE or 1 (meaning match approximately) or FALSE or 0 (meaning match exactly). For all the times I’ve used a VLOOKUP formula, I have used exact match every single time. For our example with Item IDs and Product Titles, you would simply put a 0 in the final section of our formula.
And that, my friends, is everything, and a bit more, that you need to know about VLOOKUP formulas in Excel. These formulas are a powerful tool for the PPC advertiser!
