Excel FILTER Function Using Multiple Criteria (All Matching)
Let’s start by examining the data. We have a 4-column table formatted as an official Excel Data Table named “TableDiv”.
Our objective is to filter the table to only show rows where the user works in both the “Productivity” [Division] AND is a member of the “Finance” [Department].
Start our formula using the FILTER Function.
=FILTER(We will select the entire table named “TableDiv”…
=FILTER(TableDiv,This returns the entire row of each qualifying Division/Department. We’ll see in a little bit how to return only select columns from the filter matches.
Now we define our filter criteria to only include rows where the [Division] is equal to “Productivity” (the “Productivity” choice is in cell G1).
=FILTER(TableDiv, TableDiv[Division]=G1)The results are as follows.
This has reduced the table to only Divisions that match “Productivity”. We must now reduce the list further to only include Departments that match “Finance” (the “Finance” choice is in cell G2).
The new formula is…
=FILTER(TableDiv, (TableDiv[Division]=G1) * (TableDiv[Department]=G2) )
If you’re wondering why you are multiplying two filters against one another, then you are on the right track.
By selecting (i.e., highlighting) the first filter in the formula, we are presented with a list of “true/false” responses.
This appears automatically in the latest version of Microsoft 365. If you are running an earlier version of Excel, press the F9 key to see this list of responses (make sure you press ESC to get out of the formula to not permanently change the formula.)
The “true/false” responses indicate on an item-by-item level which [Division] entries match “Productivity” and which do not.
We can perform the same check on the second filter in the formula.
The reason we multiply these results against one another is that the moment you perform any mathematical operation on Boolean values, the “true/false” responses are changed to “1/0” responses.
This means, if you multiply a “1” by another “1”, you get “1” (true).
Any other combination of (1 * 0), (0 * 1), or (0 * 0) will result in a “0” (false).
This means a full match can only occur when all tests are resulting in a “1”. Any “0” introduced into the logic will result in a “0”, effectively disqualifying the full set of tests from the result list.
Selecting both filter tests in the formula produces a list of ones and zeroes.
{0;0;1;0;1;0;0;0;0;0;0;1;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
Ultimately, the full set of tests only returns rows that evaluate to a 1 (true).
You could build on this logic to only include rows where the [Yearly Sales] are greater than $100,000.
=FILTER(TableDiv, (TableDiv[Division]=G1) * (TableDiv[Department]=G2) * (TableDiv[Yearly Salary] > G3) )
Featured Course
Master NEW Excel Functions in Office 365 & Office 2021
Excel FILTER Function Using Multiple Criteria (Partial Matching)
What if instead of a row in the table having to match ALL the defined filter criteria, we accept table rows where some (or all) criteria match? In other words, any row from the “Productivity” Division along with any row from the “Finance” Department.
This is a simple matter of replacing the multiply operators in the formula with addition operators.
This changes the results of the row-by-row tests.
{1;1;2;1;2;1;1;1;0;0;0;2;1;2;1;1;0;0;0;0;1;1;1;0;0;1;0;1;0;0;1}
The Boolean math says that if there is a “1” (true) anywhere in the result set, the result is “true”. Any value other than 0 (zero) is considered a “true” by Excel.
Excel FILTER Function Returning Selected Columns
Using our previous example of filtering for “Productivity” or “Finance”, suppose we only wish to return the [Name] and [Yearly Salary] columns.
There are tricks you can use when working with the FILTER Function (check them out in the post “Excel FILTER Function TRICK for Non-Adjacent Columns”), but since then, Microsoft has released a new function called CHOOSECOLS.
The CHOOSECOLS Function allows you to select specific columns from an array or table.
Taking our filtered result from the earlier formula…
=FILTER(TableDiv, (TableDiv[Division]=G1) * (TableDiv[Department]=G2) )Wrap this formula inside a CHOOSECOLS Function.
=CHOOSECOLS(FILTER(TableDiv, (TableDiv[Division]=G1) * (TableDiv[Department]=G2) ), 2, 4)
The CHOOSECOLS Function lets us select the second and fourth columns from the filtered results.
We can take those results to another level by sorting them in descending order by [Yearly Salary]. This is done by wrapping the current formula within a SORT Function.
=SORT(CHOOSECOLS(FILTER(TableDiv, (TableDiv[Division]=G1) * (TableDiv[Department]=G2) ), 2, 4), 2, -1)
The “2” in the SORT Function indicates the column to sort by, while the “-1” indicates the sort direction (descending).
Excel FILTER Function Using Multiple Criteria in the Same Column
It’s time for a challenge.
Suppose you wish to filter the data using the following criteria:
- The [Division] has to be either “Game” or “Utility”
- The [Yearly Salary] is greater than $80,000
- We only want the [Name] and [Yearly Salary] columns
- The results must be sorted by [Yearly Salary] in descending order
For the first requirement, we’ll use the FILTER Function and an OR operator against the first column ([Division]) of the data.
=FILTER(TableDiv, (TableDiv[Division]=G1) + (TableDiv[Division]=G2) )
For the second requirement, we will reduce the data to only include [Yearly Salary] entries that are greater than $80,000.
=FILTER(TableDiv, ( (TableDiv[Division]=G1) + (TableDiv[Division]=G2) ) * TableDiv[Yearly Salary] > G3)
Next, we will select only the 2nd and 4th columns from the table using the CHOOSECOLS Function.
=CHOOSECOLS(FILTER(TableDiv, ( (TableDiv[Division]=G1) + (TableDiv[Division]=G2) ) * TableDiv[Yearly Salary] > G3), 2, 4)
Finally, we sort the list in descending order by the 2nd column.
=SORT(CHOOSECOLS(FILTER(TableDiv, ( (TableDiv[Division]=G1) + (TableDiv[Division]=G2) ) * TableDiv[Yearly Salary] > G3), 2, 4), 2, -1)
NOTE: Pay special attention to the set of parentheses that surround the two [Division] tests. This is necessary to ensure that these tests occur independently of the [Yearly Salary] test.
When the formula gets this long, it’s easy to lose focus on what is happening.
Here’s the formula with some strategic line feeds included to help break the logic into its constituent pieces.
Final Thoughts
I hope this tutorial helps because using the FILTER Function with multiple criteria is a common question I received from our community. And I have to say, it’s not that straightforward to figure out. So, I hope you found this helpful.
Practice Workbook
Feel free to Download the Workbook HERE.
Featured Course
Power Excel Bundle
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
