Multi-Column Lookup with VLOOKUP and SUMIFS
When you need to perform a lookup, your instinct tells you to use VLOOKUP. But, when your lookup uses multiple conditions and columns, you may be inclined to use SUMIFS. However, when the value you need to return is a text string, rather than a numeric value, you are precluded from using SUMIFS since it only returns numbers. But, are you? This post demonstrates how to perform a lookup on multiple columns and return a text string by combining these two powerful functions. Excited? Me too! Let’s get to it.
Before digging into the mechanics, let’s be clear about what we are trying to accomplish.
We have a bunch of employees stored in a table named Table1, as shown below.
We are trying to build a report that retrieves the State given the employee Last and First names, as shown below.
If the report had contained the Employee ID instead of the Last and First name, the lookup would be easy, right? We could just use VLOOKUP and be done. But, our lookup needs to be performed by matching two columns, the Last and First name columns. If the value we were returning was numeric, such as the Zip code, we could use SUMIFS. But, since SUMIFS returns numbers and not text strings (such as the State), we can’t use SUMIFS. And since the lookup must be performed on multiple columns, we can’t use VLOOKUP. If we can’t use SUMIFS or VLOOKUP, then, what are we supposed to do? Well, what if we used SUMIFS and VLOOKUP in the same formula? Can we do that? Yep. Here’s how.
We essentially use SUMIFS to return the unique Employee ID value, and then we feed that value into VLOOKUP as the first argument. This means that VLOOKUP has a lookup value that is determined by the SUMIFS function. SUMIFS will retrieve the EEID for the desired employee based on the Last and First name columns, and then VLOOKUP will use that ID to perform a standard lookup and return the State. In summary, we use the SUMIFS as the first argument of the VLOOKUP. Let’s take the functions one at a time.
Here is the report.
The data is stored in Table1. Let’s take the first step with the SUMIFS function. We want it to return the unique EEID. So, we use the following formula.
- Table1[EEID] is the column of numbers to sum
- Table1[Last] is the first criteria range, the last name column
- B7 is the first criteria value, the report’s last name value
- Table1[First] is the second criteria range, the first name column
- C7 is the second criteria value, the report’s first name value
The results of filling this formula down are shown below.
We were able to use SUMIFS to retrieve the Employee ID based on matching the First and Last name values. So far so good? Now, we just need to ask VLOOKUP to return the State based on the Employee ID.
Note: it is important to note that this technique assumes that each EEID value is unique within the column, and that only one row will satisfy all SUMIFS conditions. If these assumptions are not met, this technique may not work as expected. If your data table doesn’t have a unique ID column, you can always add a helper column that numbers the records 1, 2, 3, and so on.
Let’s use the SUMIFS function above in a VLOOKUP, as shown below.
- SUMIFS(…) returns the Employee ID for use as the VLOOKUP’s lookup value
- Table1 is the lookup range, the employee table
- 5 is the column that has the value to return, the State column
- 0 tells Excel we are looking for an exact matching EEID value
After filling the formula down, our report is complete, as shown below.
We did it…yay!
Note: It is also interesting to note that this technique can be used with date columns as well, and if needed, we could use comparison operators to find values within a range of dates.
If you have any other techniques for performing multi-column lookups, please share by posting a comment below…thanks!