FILTER Function in Excel

What is FILTER Function in Excel?

The FILTER function is one of the Lookup & reference functions of Excel.

It extracts all record from the database that matches the specified conditions.

We can find this function in the Lookup & reference of the insert function Tab.

How to use FILTER function in excel

1. Click on an empty cell (like F5 ).

2. Click on the fx icon (or press shift+F3).

3. In the insert function tab you will see all functions.

4. Select Lookup & reference category.

5. Select the FILTER function.

6. Then select ok.

7. In the function arguments Tab you will see the FILTER function.

8. Array is the range or array to filter.

9. Include an array of booleans where TRUE represents a row or column to retain.

10. If_emty is returned if no item is retained.

11. You will see the result in formula result section.

Examples of FILTER function in excel

Example 1:

how to find data in a table with FILTER function

We want to find the height, age, and weight of a person named “Elijah” from the following data

=FILTER(A5:D14,A5:A14="Elijah")
answer is  {Elijah,22,198,185}

Example 2:

how to filter data in a table with FILTER function

We want to find the height, age, and weight of a person named “Noah” from the following data

=FILTER(A5:D14,A5:A14="Noah")
answer is  {Noah,25,205,235;Noah,29,199,231;Noah,22,204,190}

Example 3:

Find the multiple criteria with FILTER function

We can filter multiple criteria in Excel in different ways:

logical AND operator :

In this case, we use the multiplication operator (*) to return all the values ​​in the range of our array.

=FILTER(A6:D15,(A6:A15="Noah" )*(B6:B15=22))
answer is  {Noah,22,204,190}

logical OR operator :

In this case, we use the multiplication operator (+) to return all the values ​​in the range of our array.

=FILTER(A6:D15,(A6:A15="Noah" )+(B6:B15=22))

Example 4:

How can extract specific record from table in excel?

By using FILTER function, we can extract a specific record from table in excel

For example, in the table below, we want to know how old is benjamin?

=FILTER(A7:D16,(A7:A16="benjamin" ))
answer is  {Benjamin,21,202,235}

Example 5:

How can extract Values ​​greater or smaller than a certain number?

We can extract Values ​​greater or smaller than a certain number.

For example, in the table above, we want to know Who is taller than 201?

=FILTER(A8:D16,(C8:C16>201 ))
answer is  {Benjamin,21,202,235}

Purpose of FILTER function

It extracts all records from the database that matches the specified conditions.

Return value  of FILTER function

It can return any type of data (number,text,….).

FILTER (array, include, [if_empty])=number,text,....

Arguments of FILTER function

FILTER(array, include, [if_empty])

FILTER function has 3 arguments.

• array you can enter the range of cells that make up the list or database
• include is either the label of the column in double quotation marks
• [if_empty] is the range of cells that contains the conditions you specify

Which version of excel supports FILTER function?

This function is available for excel versions (2021).

Errors in FILTER function

If no record matches the criteria, FILTER returns the #VALUE! error value.

If more than one record matches the criteria, FILTER returns the #NUM! error value.

FILTER related functions :

• UNIQUE function
• SORT function
• SORTBY function
• SEQUENCE function
• RANDARRAY function