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**.

**Table of Contents**hide

**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.

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","")`

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**.

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,"")`

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"))`

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.

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"))`

**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"))`

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.**

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"))`

**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))))`

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.

** ISNUMBER(MATCH(D4:D24,{"Brazil","Argentina","Uruguay"},0)) **converts the numbers into

**TRUE**and the errors into

**FALSE**.

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.

So **( ISNUMBER(MATCH(**

`D4:D24`

`,{"Brazil","Argentina","Uruguay"},0)))+(ISNUMBER(MATCH(`

`E4:E24`

**returns a**

`,{"Brazil","Argentina","Uruguay"},0))) `

**1**or

**2**if either a South American country is champion, or runners up or both.

And returns zero otherwise.

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))))`

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)))`

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.

** ISNUMBER(SEARCH("*Germany",D4:D24)) **converts the 1’s into

**TRUE**, and the errors into

**FALSE**.

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)))`

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,"")`

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`

`,"")`

**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

- How To Search Multiple Items in Excel Filter
- How to use VBA DIR function in Excel (7 Examples)
- How to Filter Cells with Formulas in Excel (2 Ways)
- How to Filter Multiple Columns Simultaneously in Excel (3 Ways)
- How to Apply Multiple Filters in Excel [Methods + VBA]
- How to Filter Data in Excel using Formula
- Excel Filter Data Based on Cell Value (6 Efficient Ways)

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.