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
New Sessions. New Networking. An All New Experience.
When we get feedback on ways to make Hero Conf more relevant, more actionable, and more meaningful, we take it pretty seriously. It's our most powerful tool in creating an event we believe helps our industry thrive. And we've used that information to build our...
Why Digital Marketing: Traditions Aren’t Always the Best
Explore how digital marketing can take your business goals to the next level and start expanding into new markets.
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
PPC Budgets: Pass Go into 2018 and Collect Optimal ROI
In this webinar, Shape CEO Jon Davis and Hanapin Director of Services Jeff Baum will share their expert advice about how you can perfect your 2018 budget strategy to achieve optimal ROI in the new year.
Setting Up and Analyzing Universal App Campaigns
Now that UAC adoption is mandatory for promoting an app on AdWords, it's important to compare Universal App Campaigns vs Mobile App Install Campaigns.
RStudio for PPC: A Beginner's Intro
A beginner's intro to using RSudio for PPC analysis and reporting.