

There are many cases in which you want to conduct a lookup with several search criteria. As of now only the SUMIFS formula allows a multi-condition lookup. Unfortunately, SUMIFS only works for numeric values (including dates) as the return value. If you want to return text, there is no direct method.
The good news: Both major lookup formulas besides SUMIFS (VLOOKUP, and INDEX/MATCH) allow workarounds. The easiest way is usually an additional helper column. But also array formulas can be used for multi-condition lookups – even without helper columns.
Contents
Example for all methods: Return the revenue based on the category, region and year.
In this article, you explore five methods for multi-condition lookups. All methods will be introduced using the same example like shown in the screenshot on the right-hand side.
You have a table containing sales data with the columns “Category” (column C, e.g. “Computers”, “TVs”), “Region” (column D, e.g. “Asia”, “Europe”) as well as the “Year” (column E, e.g. “2016”). The return value (here: “Revenue”) is provided for each combination of category, region and year in column F. The cells J3 to J5 contain selection fields for each of the columns. For example, you want to return the revenues of TVs in Europe in the year 2016. The return value should be shown in the cell J8 to J12, depending on the method in this chapter.
As mentioned before, there is no direct way to conduct a lookup with several conditions using VLOOKUP formula. But there is a simple workaround: Insert a new “primary key” column. To achieve this, concatenate the different search values in a new (leftmost) column.
The first step is to insert the new column B with the new primary key. You can easily concatenate the values from the columns C, D and E by using the “&” sign. The formula in B4 is =C4&D4&E4 .
In the second step, you set up the VLOOKUP formula. The search term is not one single search criteria. Instead, you search for the combination of “Category”, “Region” and “YEAR”. The search range starts now with column B which contains the new primary key. The return value is located in column F so that the column number is 5. As usual the last argument of the VLOOKUP formula is “FALSE” because you search for the exact search phrase. The complete VLOOKUP formula is: =VLOOKUP(J3&J4&J5,B:F,5,FALSE)
Method 1 for a multi-conditional lookup uses the VLOOKUP formula and a new primary key in column B.
Please note the following comments.
The first method in the previous pages of the multi-condition VLOOKUP required an additional helper column. The method in this section is a little bit more complex but has the advantage that it doesn’t need an additional column.
Structure of the multi-condition VLOOKUP formula.
Because the underlying formula of this method is still VLOOKUP, the arguments are more or less the same. The following numbers correspond to the numbers in the screenshot on the right-hand side, which shows the structure of the VLOOKUP/array formula.
Applying this structure to the example in this chapter you get the following formula.
{=VLOOKUP(J3&J4&J5,CHOOSE({1,2},C4:C51&D4:D51&E4:E51,F4:F51),2,FALSE)}
Method 2 for a multi-conditional lookup uses the VLOOKUP formula as an array formula.
Please note the following comments:
The third method for a multi-conditional lookup uses the INDEX/MATCH formula and an additional helper column. It pretty similar to method 1 before because the helper column works the same way. Besides that, it’s based on a conventional INDEX/MATCH formula.
The idea of this method is to create a helper column in which you concatenate all search criteria. Unlike the helper column of method 1 (VLOOKUP and helper column), the helper column can be located anywhere on your worksheet. It doesn’t have to be on the left-hand side of the return column.
The formula in the helper column (here: cell B4) is =C4&D4&E4 .
You apply a “normal” INDEX/MATCH formula with the only difference that you search for the combination of criteria within the MATCH part of the formula. The formula (here in cell J10) is =INDEX(F:F,MATCH(J3&J4&J5,B:B,0)) .
Method 3 for a multi-condition lookup uses the INDEX/MATCH formula combination and a new primary key in column B.
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
Learn more or get it on Amazon!
A helper column always means additional work and in some cases, you want to leave the raw data untouched. The INDEX/MATCH formula combination can also be used without inserting a helper column. Like the method 2 before, the INDEX/MATCH formula is used like an array formula.
Structure of the multi-condition INDEX/MATCH formula.
The structure of the multi-condition INDEX/MATCH formula is shown in the screenshot on the right-hand side. The following numbers relate to this figure.
Applying this structure to the example of this chapter leads to the following formula.
{=INDEX(F4:F51,MATCH(J3&J4&J5,C4:C51&D4:D51&E4:E51,0))}
Method 4 for a multi-condition lookup uses the INDEX/MATCH formula as an array formula.
Hold on a second. Was this information helpful so far?
Why don't you subscribe to our monthly, free Excel newsletter?
Subscribe now! Your welcome gift: Our big 45 pages keyboard shortcuts package. In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Of course, I'm also on other networks:
Twitter: Follow @professorexcel
Facebook:
If your return value is a numeric value (including a date), you can use a SUMIFS formula without any additional modifications. The structure is described in detail starting in this article.
The example using the SUMIFS formula is shown in screenshot below.
Method 5 for a multi-conditional lookup uses the SUMIFS formula.
Please note the following comments.
All methods described above work in a horizontally the same way with two exceptions.
All examples are also included horizontally in the download file below.
Was the information helpful in this article?
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.