Troubleshooting Lookup Functions in Excel
December 18, 2017
Last week, Hanapin hosted an Excel Extravaganza, which was a compilation of 3 Excel for PPC webinars in 3 days. On day 3, Lara and I discussed bidding methods with Excel. I specifically discussed bid tables and how to use Lookup functions to automatically pull bid changes from a bid table. We discussed how to write lookup functions, specifically the Index + Match, but I want to follow up with a quick guide to troubleshooting Lookup functions.
It’s easy to make mistakes when writing new functions, and receiving errors in Excel is often what intimidates people from using Excel more often. My goal with this post is to make it easy to find and correct your mistakes when writing lookup functions in Excel so that you’re not afraid to use them! (If you didn’t catch the webinars, check them out. If you want a written explanation of the Index + Match, check out this post.)
As I mentioned, the bid table method I taught in the webinar focused on the Index and Match functions. When used alone, the Index function is relatively simple. There’s only one main error I see people make when writing an Index function without a Match:
In the example above (from the webinar), you can see that we have a simple Index function with three arguments: array, row_num, column_num. The Array argument is the table of data from which we want to pull bid change percentages. The most common error I see is that people forget to anchor ($A$1) their Array. When that happens, the first cell with this Index function works fine. However, if you copy that function down a full column, you’ll begin to get the wrong answers and eventually you’ll receive errors. When the Array is NOT anchored, the cell references move in the same direction as the function that’s being copied down or across an Excel worksheet.
Just remember: in 95% of Excel functions, you should anchor ranges of data.
Index + Match
As a reminder, here is the definition and the makeup of a Match function:
When we add the Match function into an Index, it naturally becomes more difficult to troubleshoot as the number of arguments has doubled or even tripled. Here are the most common errors I see with Match functions:
Both the Index and Match functions have an Array argument, which often leads people to believe those should be the exact same range of data. This is WRONG. The Array in a Match can ONLY be one row OR one column of data. The Array for and Index is usually multiple rows and columns.
The second-most common mistake I see is that people forget the third argument: Match Type. This argument is tricky! It’s in brackets because it’s optional. However, the default that Excel uses if the argument is “1”, which is not always what the user intended. Below are the options we’re given for the Match Type argument.
VLookup and HLookup Functions
I did not cover the VLookup or HLookup functions in the webinar, but they’re often used instead of the Index + Match functions. They’re less flexible than the Index+Match, so there are some instances where Index+Match is the ONLY lookup function that will work. However, many people don’t run into those scenarios and find the VLookup and HLookup functions easier to understand. Here are the most common pitfalls I see with these lookup functions:
The “V” in VLookup stands for “vertical”. This is key to understanding the arguments of this function. The Lookup_Value MUST be located in the first (vertical) column of the array. The example below shows an example of this mistake.
The Lookup_Value argument shows that I’m looking for “Keyword 2”. For this function to work, the keywords need to be in the FIRST column of the array I’m using. However, I’m using an array from A-E, meaning the first column is the campaign name. When the function can’t find the value you’re looking for, you’ll get an “#N/A” error.
The HLookup is nearly identical to the VLookup, except that it’s meant for horizontal data tables (hence the “H”). That means the most common errors are basically the same. The Lookup_Value MUST be located in the first (horizontal) row of the array.
In addition, it’s easy for people to forget the optional fourth argument of an Hlookup or a Vlookup: the range_lookup. The range_lookup argument has two options: True or False. When this argument isn’t specified by the user, Excel defaults to True. The True option means your function will look for an APPROXIMATE match to the lookup_value you’ve specified. If you’re looking for an exact keyword, exact number, or an exact piece of text you need to use False.
Lookup functions can be very helpful for PPC analysis and PPC automation. However, it’s easy to get discouraged from using Excel when we run into errors. I hope this post helps you all to troubleshoot your Lookup functions a little faster so you don’t refrain from using them!
Browse By Category
Why You Should Test Target ROAS Bidding for Shopping Campaigns
The case for testing Target ROAS bidding for AdWords shopping campaigns, plus tips on how to successfully set up such a test.
10 New Ways to Optimize Paid Search with Call Intelligence
Mobile search drives billions of calls to business each year, and calls convert at a higher rate than digital leads. When properly optimized, calls can have a transformational impact on your bottom line. Join this webinar to learn tactical tips and smart strategies to boost...
Google Adds New Tools to Highlight Physical Store Locations
Buying in-store is still a big deal for shoppers, and Google has announced a few new tools to let physical retailers target those shoppers heading in stores.
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
Spend Smarter: Identifying Account Opportunities
Explore a method for projecting performance that makes it easier to provide solid budget increase recommendations and also alleviates some stress.
Up and Running with AdWords Add-on for Google Sheets
In this article we will set up for the AdWords add-on for Google Sheets. By the end you'll set up your own reports and automate the data gathering portion of your workflow.
Tackling Facebook Ad Disapprovals
Why your Facebook ad is disapproved and what you can do to fix it and avoid future problems.
Load More »