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

- 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 t** he 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?

Name | Age | Height | Weight |

Olivia | 25 | 210 | 180 |

Noah | 25 | 205 | 235 |

Oliver | 27 | 195 | 205 |

Elijah | 22 | 198 | 185 |

James | 29 | 199 | 231 |

William | 29 | 201 | 240 |

Benjamin | 21 | 202 | 235 |

Lucas | 25 | 200 | 238 |

Henry | 22 | 204 | 190 |

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

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

## Return value of **FILTER** function

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

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

## Arguments of **FILTER** function

**FILTER****FILTER(array, include, [if_empty])**

**FILTER** function has 3 arguments.

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

## Which version of excel supports **FILTER** function?

**FILTER**This function is available for excel **versions **(2021).

## Errors in **FILTER** function

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

**FILTER**- UNIQUE function
- SORT function
- SORTBY function
- SEQUENCE function
- RANDARRAY function