# How To Perform Excel Lookups With Multiple Criteria

By Jacob Fairclough | @SomeSecretJake | Associate Director of Analytics at Hanapin Marketing

*March 7, 2016*

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.

## Simplest Solution: Helper Columns

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.

## Simple Solution Sidebar: Skipping The Lookup Entirely

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.

## More Advanced Solution: Index Match With Arrays

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.

## Going All In Solution: Introducing Choose

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.

## Conclusion

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

## Remarketing to User Actions Instead of Page Visits

Explore how to use Google Tag Manager to segment your remarketing audiences to test out new user behavior such as user actions.

## In-Market Audiences: Bing's Latest Targeting Feature

Bing Ads recently released a new intent based audience targeting for search campaigns called in-market audiences.

## Automated Bidding Strategies By The Numbers

Explore the numbers that will be useful in determining which automated bidding strategy aligns with your goals and the data needed to get that strategy off the ground.

### Hanapin

### NEWSLETTER

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

## Responsive Search Ads: Why We Love/Hate Them

We've had some time to try out the new RSA's, and it's time for an honest discussion.

## Facebook Ads Auction: How Does It Work?

Curious as to how Facebook Ads Auction works? Get a breakdown of the auction components to get a better understanding.

## New Research! The State of Paid Social

In our new 2018 State of Paid Social report, we share the latest data on paid social advertising trends in a variety of networks including Facebook, Linkedin, Instagram, Pinterest, and Youtube, collected from over 400 marketers at brands and agencies.

Load More
»