VLOOKUP on Two or More Criteria Columns
If you have ever tried to use a VLOOKUP function with two or more criteria columns, you’ve quickly discovered that it just wasn’t built for that purpose. Fortunately, there is another function that may work as an alternative to VLOOKUP depending on what you want to return.
Multi-Column Lookup Objective
First, let’s confirm our objective by looking at a sample workbook. We have exported some information from our accounting system, and it is basically summarizes the transaction totals for the month by class and by account. A sample of the export is shown below:
From this exported data, we would like to retrieve selected amounts based on the class and account columns. We want to retrieve the amounts and place them into our little report, pictured below:
If you are familiar with the VLOOKUP function, it feels natural to try to build the report with this function because, after all, this is a lookup task. And, lookup tasks are best solved with traditional lookup functions…right? Well, it depends. It depends on what you are trying to retrieve.
Conditional Summing for Lookups
If you are trying to retrieve a numeric value, such as an amount, then a traditional lookup function may not be your best bet. Here’s why. Beginning with Excel 2007, Microsoft included the conditional summing function SUMIFS. This multiple condition summing function is designed to add up a column of numbers, and only include rows that meet one or more conditions. Are the dots starting to connect yet?
If we apply this idea to our task at hand, we would quickly realize that we could use this conditional summing function to retrieve our report values.
The first argument of the SUMIFS function is the sum range, that is, the column of numbers to add. In our case, the column that has the value we wish to return. The remaining arguments come in pairs: the criteria range and the criteria value.
It is helpful to think about the function in these terms: add up this column (argument 1), only include those rows where this column (argument 2) is equal to this value (argument 3), and where this column (argument 4) is equal to this value (argument 5), and where…and so on, up to 127 pairs.
Thus, to populate our report, we’ll retrieve the amount values from the export, and match the class and account columns, as shown below.
If there happen to be multiple rows with the same class and accounts, then the SUMIFS function would return the sum of all matching items.
As you can see, if the value you are trying to return is a number, then the SUMIFS function makes it simple to perform multi-column lookups. But, what if the value you are trying to return is not a number? Well, then you’ll need to use a traditional lookup function as discussed below.
Using VLOOKUP with SUMIFS Method
One method is to use VLOOKUP and SUMIFS in a single formula. Essentially, you use SUMIFS as the first argument of VLOOKUP. This method is explored fully in this Excel University post:
Using VLOOKUP with CONCATENATE Method
If you are trying to return a text string rather than a number, or are using a version of Excel that doesn’t have SUMIFS, then you are probably stuck with using a traditional lookup function such as VLOOKUP along with the CONCATENATE function to generate a single unique lookup column. This approach is fairly well documented, but the basic idea goes like this: create a single lookup column first, and then use VLOOKUP.
Our example will be an employee list, as illustrated below:
We need to retrieve the state from the employee list for our little report shown below:
Since the value we are trying to return, the state, is a text string and not a number, we are precluded from using the SUMIFS function. Thus, we’ll need to go old-school with VLOOKUP and CONCATENATE.
We start by building a helper column that basically creates the combined lookup values. This can easily be accomplished with the CONCATENATE function or the concatenation operator (&). This new lookup column is illustrated in column B below:
Now we have a single lookup column that can be used with a traditional lookup function such as VLOOKUP. The report can be populated by looking up the combined names within the new lookup range, as shown below:
This same approach can be used when two, three, or more lookup columns need to be considered.
In addition to being able to perform multi-column lookups when the return value is numeric, the SUMIFS function has additional benefits when compared to traditional lookup functions. For example, it returns zero when no matching value is found, it returns the sum of all matches, it supports comparison operators, and it won’t break when a new column is inserted between the lookup and return columns.
So, when you are about to bust out the VLOOKUP function to do a lookup task, consider using SUMIFS instead. Believe it or not, the SUMIFS function makes a wonderful lookup function.
If you have any other preferred approaches to multi-column lookups, we’d love to hear more…please post a comment below.
If you want to play with the workbook used to generate the screenshots above, please feel free to download the sample file:
This post is a summary adaption of Excel University Volume 2, Chapter 15 Perform Lookups with SUMIFS. More information available:
If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.