Troubleshooting Lookup Functions in Excel

By Alaina Thompson | @AlainaPThompson | Account Manager at Hanapin Marketing

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.)

Index

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:

Bid Table with Index Function

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:

Match Function Definition

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.

Match Function in Excel

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.

VLookup 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.

HLOOKUP

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.

Conclusion

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!

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...