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 ).
Click on an empty cell

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

fx icon

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

insert function tab

4. Select Lookup & reference category.

5. Select the FILTER function.

excel MATCH 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

how to find data in a table with FILTER function
=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

how to filter data in a table with FILTER function
=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.

Find the multiple criteria with FILTER function
=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.

Find the multiple criteria with FILTER function
=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?

NameAgeHeightWeight
Olivia25210180
Noah25205235
Oliver27195205
Elijah22198185
James29199231
William29201240
Benjamin21202235
Lucas25200238
Henry22204190
=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?

How can extract Values ​​greater or smaller than a certain number?
=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 :

Leave a Reply

Your email address will not be published. Required fields are marked *