## Reframing The Conversation: The Benefits Of Profit-Based PPC Management

Explore how a profit-based management approach can unlock growth opportunities in your accounts!

By Jacob Fairclough | @SomeSecretJake | Senior Account Analyst at Hanapin Marketing

Everyone loves Vlookups, the formula that helps us quickly match data across our ever-increasing Excel tables. However, there is a glaring weakness – lookups for multiple criteria. Vlookup accepts one value in its first parameter. Need more? Well, that is too bad as the technology just isn’t there yet so break out your pencil and paper, you’re going to be here a while.

Now that you’ve braced yourself for the impending slog, I’m happy to tell you there is an easier way. Depending on how adventurous you are feeling, you have quite a few options. In this article, we’ll discuss Vlookup examples for the purists and a couple of other functions for those who want to live more wildly.

The simplest solution involves introducing at least two columns to our buddy, concatenation. By combining multiple cells into one value, we can use the concatenated cells as our look up, bypassing the need for any sleights of hand or Excel sorcery.

For example, we have the same ad group name in multiple campaigns. If we look up by ad group we are not guaranteed to find the correct ad group. The lookup will return the first matching ad group it finds. If we need to match the campaign and ad group, we can combine the two and match that value instead.

You can even concatenate with special characters to make it easier to break them down later. With a combination such as “Campaign | Ad Group”, you not only make it more readable but establish a clear indicator for division between criteria. If you need to reverse this, you can use text to columns to break them back down into separate columns.

In some cases, you can skip the Vlookup entirely and use SumIfs. SumIfs allows you to sum values based on multiple criteria.

The downside to this method is that SumIfs works with numbers but not with strings. This means you can return numbers (clicks, impressions, cost, etc.) but not strings (status, descriptions, IDs, etc.).

In this case, we use the campaign and ad group column as our criteria and when both match, we sum up the cost. While this isn’t as fancy as the other solutions, it’s extremely convenient.

We’ve covered index match before as a more flexible alternative to Vlookup. Combining it with arrays and simple logic turns the functions into a multi-criteria lookup. In essence, we’ll choose our lookup column and the ranges containing our criteria. If all of them evaluate to TRUE we get a match and return the appropriate value.

{=Index(Return_Range(Match(1,(Criteria_Range_1 = Criteria_1) * (Criteria_Range_2 = Criteria_2),FALSE))}

Since this is an array formula you’ll need to execute it with CTRL+Shift+Enter or CMD+Shift+Enter to get the correct results. If it helps, the match segment is translated as check these ranges for a value of 1. Why are we looking for 1? I’ll give you a hint – it’s a logic problem.

We often use TRUE and FALSE as they are equal to 1 and 0 respectively. When we run the match function, it looks through each column for a match. If there is a match it returns 1, if not it returns 0.

Notice the multiplier between the two as anything multiplied by 0 evaluates to 0. As a result, the only time it will return 1 is if we match both criteria and it evaluates as 1*1, which equals 1.

The first solution included helper columns. This requires inserting additional rows into your spreadsheets. That isn’t a major problem but it can be annoying and require a little extra formatting and clean up. What if you could do all of this without inserting the columns?

Welcome the Choose function. By utilizing an array function we can create a virtual table for our Vlookup, skipping the need to manipulate the original.

Unlike the last example, we’ll go back to our old pal, Vlookup. In this case, we’ll choose the values we are looking for, set the lookup table, and so on. Only this time the table will be virtual so we’ll build it inside the function!

{=Vlookup(Criteria1&Criteria2,choose{1,2}Criteria1_Range&Critera2_Range,Lookup_Range),2,FALSE)}

Similar to the previous example, the middle is where the magic happens. This explanation requires a bit of reading on your own but the quick version is, the Choose function helps build a virtual table of two ranges. In this example it merges the two criteria into a virtual helper column with the second column being the values we want to return.

Then we continue the Vlookup as normal. Our values are in the second column we set up, so we set it to 2 and it runs just like any other Vlookup.

Some of these formulas will take a bit of practice before you are conformable with them. Most of them have very similar outputs, though, meaning you can choose whichever you prefer. Although we only used two criteria in our examples, all of these formulas can be extended to include more. Play around with the examples in your own workbooks and see what works best for you. You might even stumble across your own solution or style.

Browse By Category

Explore how a profit-based management approach can unlock growth opportunities in your accounts!

After a few months of testing and collecting data in Quora, explore the details of which targeting is working and what isn’t for our team in Quora.

There's no better time than now to dive into your accounts and do some clean-up with these 5 simple PPC tasks!

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

Skip the hundreds of emails and articles and instead join us February 20th - 23rd to learn how to get guaranteed results from your marketing efforts in 2018, and beyond.

Having Google Tag Manager know-how is an essential part of PPC. Learn the basics for AdWords and Bing!