Filter Multiple Criteria in Excel (4 Suitable Ways)

FILTER Function in Excel

Today I will be showing how you can filter out some data matching multiple criteria using the FILTER function of Excel.

Before going to the main discussion, I would like to remind you one thing.

The FILTER function is only available in Office 365.


Download Practice Workbook


An Introduction to Excel’s FILTER Function

Let’s be introduced with Excel’s FILTER function first.

Look at the data set below. We have the years, the host countries, the champion countries, and the runners-up countries of all the FIFA World Cups in columns B, C, D, and E respectively.

A Data Set in Excel

Now if I ask you, what are the years when Brazil became champion?

What will you do?

You will probably go through column D (Champion), and see whether there is a cell containing a Brazil in it or not.

Then when you find one, you will move two steps left of that cell to column B (Year), and note down the corresponding year.

And then you will again go down through column D and do the same for all the cells containing a Brazil in it.

Thus, you will note down all the years when Brazil was champion.

For a small set of data, this is OK. But can you repeat the same procedure for a large set of data, think, of 10000 rows?

Read More: How to Filter Multiple Rows in Excel (11 Suitable Approaches)

The answer is no, a big no.

So what to do?

Microsoft Excel brings a built-in function called FILTER to perform exactly the same task for you.

The FILTER function takes three arguments, a range of cells called array, a criterion called include, and a value called if_empty that is returned in case the criterion is not met for any cell.

So the syntax of the FILTER function is:

=FILTER(array,include,[if_empty])

For better understanding, let’s come to the Brazil problem. We have to filter out the years when Brazil became champion.

The formula to accomplish this will be:

=FILTER(B4:B24,D4:D24="Brazil","")

FILTER Function in Excel

See, we have got all the years when Brazil became champion, 1958, 1962,1970, 1994 and 2002 (Colored in the Image).

Now for the sake of understanding, let’s break down the formula.

D4:D24="Brazil" goes through all the cells from D4 to D24 and returns a TRUE if it finds a Brazil, otherwise FALSE.

Logical Function in Excel

The formula FILTER(B4:B24,D4:D24="Brazil","") then becomes

=FILTER({B4,B5,B6,...,B24},{FALSE,FALSE,...,TRUE,...,FALSE},"")

For each TRUE, it returns the adjacent cell from the array {B4,B5,B6,...,B24}

And for a FALSE, it returns no result, “”. (This is optional. Default is no result, “”)

There is a TRUE for only the cells B9, B10, B12, B18, and B20.

So it returns only the contents of these cells, 1958, 1962, 1970, 1994, and 2002.

These are the years when Brazil became champion.

Hope you have understood how the FILTER function works.

Now, if you understand this, can you tell me the formula to find out the years when the host country became champion?

Yes. You are right. The formula is:

=FILTER(B4:B24,C4:C24=D4:D24,"")

A FILTER Function of Excel

See, the host country became champion in 1930, 1934, 1966, 1974, 1978, and 1998.


Filter Multiple Criteria in Excel

Now we have understood how the FILTER function works.

Let’s try to apply multiple criteria within the function this time.


1. Multiple Criteria of OR Type

First of all, let’s focus on multiple criteria of OR type.

These are the criteria that are satisfied when any one or more than one criteria are satisfied.

For example, from the above data set, if I ask you, tell me one year when Argentina became champion or West Germany became runners-up.

You can tell either 1978, or 1982 or 1986.

Now, let’s try to filter out all the years when Italy was either the host or the champion, or both.

This is a problem of OR type multiple criteria.

It’s easy. Just add the two criteria with a plus (+) sign.

The formula will be:

=FILTER(B4:B24,(C4:C24="Italy")+(D4:D24="Italy"))

FILTER OR Type Multiple Criteria in Excel

See, Italy was either the host or champion or both in the years 1934, 1938, 1982, 1990, and 2006.

Now, for the sake of understanding, let’s break down the formula.

C4:C24="Italy" returns an array of TRUE or FALSE. TRUE when Italy was the host, FALSE otherwise.

D4:D24="Italy" also returns an array of TRUE or FALSE. TRUE when Italy was the champion, FALSE otherwise.

(C4:C24="Italy")+(D4:D24="Italy") adds two arrays of Boolean values, TRUE and FALSE. But it considers each TRUE as a 1, and each FALSE as a 0.

So it returns a 2 when both criteria are satisfied, a 1 when only one criterion is satisfied, and a 0 when no criterion is satisfied.

Two Boolean Arrays Added in Excel

The formula now becomes:

=FILTER({B4,B5,B6,...,B24},{0,2,1,...,0})

It considers the numbers greater than zero (0 and 1 here) as TRUE and the zeros as FALSE.

So it returns the years from column B when it faces a number greater than 0, and returns no result otherwise.

Now, if you understand how the FILTER function works with multiple criteria of OR type, can you give an answer to one question?

What will be the formula to filter out the years when Brazil became the champion or Italy became the runners up or both?

Yes. You are right. The formula will be:

=FILTER(B4:B24,(D4:D24="Brazil")+(E4:E24="Italy"))

FILTER Multiple Criteria of OR Type in Excel


2. Multiple Criteria of AND Type

Now we shall focus on multiple criteria of AND types.

That means we have to satisfy all the criteria to get a TRUE result, otherwise FALSE.

We know, up to the year 1970, the FIFA world cup was called the “Jules Rimet” trophy. After 1970, it started to be named the FIFA world cup.

So my first question, what are the years when Brazil won the “Jules Rimet” trophy?

There are two criteria here.

First, the year must be less than or equal to 1970.

Second, the champion country has to be Brazil.

And both the criteria are to be met.

How to accomplish this task?

Quite simple. Multiply the two criteria inside the FILTER function with a (*) sign this time.

So the formula will be:

=FILTER(B4:B24,(B4:B24<=1970)*(D4:D24="Brazil"))

FILTER AND Type Multiple Criteria in Excel

See, up to 1970, Brazil won three times, in 1958, 1962 and 1970.

Now let’s break down the formula.

(B4:B24<=1970 returns a TRUE if the year is less than or equal to 1970, otherwise FALSE.

(D4:D24="Brazil") returns a TRUE if the champion country is Brazil, otherwise FALSE.

(B4:B24<=1970)*(D4:D24="Brazil") multiplies two arrays of TRUE and FALSE, but considers each TRUE as 1 and each FALSE as 0.

So it returns a 1 if both the criteria are met, otherwise it returns a 0.

Two Boolean Arrays Multiplied in Excel

Now the formula becomes:

=FILTER({B4,B5,B6,...,B24},{0,0,...,1,1,...,0})

It returns the year in column B when it faces a 1 and returns no result when it faces a 0.

Thus we can filter any data satisfying multiple criteria of AND type.

Now can you tell me the formula to find out the years before 2000 when Brazil was the champion and Italy was the runners up?

The formula will be:

=FILTER(B4:B24,(B4:B24<2000)*(D4:D24="Brazil")*(E4:E24="Italy"))

FILTER Multiple Criteria of AND Type in Excel


3. Multiple Criteria of Combination of AND and OR Type


Case 1: OR within OR

Now if I ask you a question, what are the years when a South American country (Brazil, Argentina, or Uruguay) was either champion or runners-up?

Can you give the answer to my question?

Notice carefully. Here the champion country has to be Brazil, Argentina or Uruguay.

Or the Runners-Up country has to be Brazil, Argentina, or Uruguay.

Or both.

This is a problem of OR within OR type.

Do not worry. The formula here will be:

=FILTER(B4:B24,(ISNUMBER(MATCH(D4:D24,{"Brazil","Argentina","Uruguay"},0)))+(ISNUMBER(MATCH(E4:E24,{"Brazil","Argentina","Uruguay"},0))))

FILTER OR within OR Type Multiple Criteria in Excel

See, we have found all the years when a South American country was either champion or runners-up.

Let’s break down the formula now.

MATCH(D4:D24,{"Brazil","Argentina","Uruguay"},0) returns 1 if the champion team is Brazil, 2 if the champion team is Argentina, 3 if the champion team is Uruguay, and an error (N/A) if the champion team is none of them.

MATCH Array Formula in Excel

ISNUMBER(MATCH(D4:D24,{"Brazil","Argentina","Uruguay"},0)) converts the numbers into TRUE and the errors into FALSE.

ISNUMBER Array Formula in Excel

Similarly, ISNUMBER(MATCH(E4:E24,{"Brazil","Argentina","Uruguay"},0)) returns a TRUE if the runners-up country is either Brazil, Argentina or Uruguay. And FALSE otherwise.

An ISNUMBER Array Formula in Excel

So (ISNUMBER(MATCH(D4:D24,{"Brazil","Argentina","Uruguay"},0)))+(ISNUMBER(MATCH(E4:E24,{"Brazil","Argentina","Uruguay"},0))) returns a 1 or 2 if either a South American country is champion, or runners up or both.

And returns zero otherwise.

Two ISNUMBER Array Formulas Added in Excel

The formula becomes:

=FILTER({B4,B5,...,B24},{2,0,0,2,...,1,0})

It returns a year from column B if it finds a number greater than zero, returns no result otherwise.


Case 2: OR within AND

If you understand the above formula, can you tell the formula to determine the years when both the champion and runners-up were from South America (Brazil, Argentina, or Uruguay)?

Quite easy. Just replace the (+) sign of the previous formula with an (*) sign.

=FILTER(B4:B24,(ISNUMBER(MATCH(D4:D24,{"Brazil","Argentina","Uruguay"},0)))*(ISNUMBER(MATCH(E4:E24,{"Brazil","Argentina","Uruguay"},0))))

FILTER OR within AND Type Multiple Criteria in Excel

See, these happened only twice, in 1930 and 1950.


4. Criteria with a Partial match

Now if you notice more carefully, you will find that up to the year 1990, there was a country called West Germany.

And after 1990, there is no West Germany. What there is Germany.

The two are actually from the same country. In 1990, the two Germany (East and West) united to form present Germany.

Now can you identify the years when Germany was champion? No matter East or West.

You have to make a partial match here.

The formula will be:

=FILTER(B4:B24,ISNUMBER(SEARCH("*Germany",D4:D24)))

FILTER Partial Match in Excel

See Germany was champion 4 times. 3 times by West Germany and 1 time by present Germany.

Now let’s break down the formula.

SEARCH("*Germany",D4:D24) searches for anything having Germany in the end in the array D4 to D24. If you need Germany in the middle, use “*Germany*”.

It returns a 1 if it finds a match (West Germany and Germany) and returns an Error otherwise.

SEARCH with Partial Match in Excel

ISNUMBER(SEARCH("*Germany",D4:D24)) converts the 1’s into TRUE, and the errors into FALSE.

ISNUMBER Function in Excel

Finally, FILTER(B4:B24,ISNUMBER(SEARCH("*Germany",D4:D24))) returns the years from column B when it faces a TRUE, otherwise returns no result.

Now, if you understand this formula, can you find out the years when the FIFA World Cup was hosted by two countries?

I am giving you a clue. There must be an ” and “ in the host country name. (“and” in-between two spaces)

Yes. You are right. The formula will be:

=FILTER(B4:B24,ISNUMBER(SEARCH("* and *",C4:C24)))

FILTER Partial Match

We see this happened only once in 2002, hosted by South Korea and Japan.


Alternative Options

The methods mentioned above are quite useful. But with one disadvantage, the FILTER function is available in only Office 365.

Those who do not have an Office 365 subscription, can use these alternative methods to filter some data with multiple criteria.

To find out the years when Italy was the host country or champion, use this formula:

=IF((C4:C24="Italy")+(D4:D24="Italy"),B4:B24,"")

OR Type Multiple Criteria Using IF Function in Excel

And to find out the years when Brazil was champion up to 1970, use this formula:

=IF((B4:B24<=1970)*(D4:D24="Brazil"),B4:B24,"")

AND Type Multiple Criteria Using IF Function in Excel

Note: You can not have the blank cells removed like the FILTER function in this way. And press Ctrl + Shift + Enter to enter the formulas.


Conclusion

Using these methods, you can filter out any data maintaining multiple criteria in Excel. Do you know any other method? Let us know in the comment section.


Further Readings

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

1 Comment
  1. Reply
    Kanhaiyalal Newaskar. Jul 28, 2021 at 10:55 AM

    Sir, 28th July,2021.

    I am impressed by the example given by you,i.e. Filter Function.
    Hope to receive more functions in future too.
    Thanking you.

    Kanhaiyalal Newaskar.

Leave a reply

ExcelDemy
Logo