A scenario to use the Google Sheets FILTER function is when you want to filter data in a range based on one or more condition and get that data. If the matching data in the source range change, then the data already retrieved will update itself automatically.

The FILTER function gets the data to the left of the matching condition also, unlike the VLOOKUP, function which extracts data only in the same column that has the search key or to the right of the search key.

If you want to extract data from one or more rows and or columns, based on one or more conditions, then the powerful FILTER function in Google Sheets is the answer.

Here's the format of the FILTER function:

=FILTER(range, condition1, [condition2], ...)

= (equal-to symbol) indicates what follows will be a formula.

FILTER is the name of the function.

range is an area of the spreadsheet the data in which you want to filter.

condition1 is the first data filter condition.

condition2 is optional, and is the second data filter condition.

Let's look at an example of the FILTER function:

Here is a data range:

A B C D

NAME DEGREE FIELD EXPERIENCE (years)
Revathi BS CS 3
Bob MS EE 2.8
Arjun MS CS 5.5

Now, we will create the condition range with the conditions DEGREE=MS and FIELD=EE. That is, we want to find the name, degree, field, and experience of employee(s) who have a master of science degree (MS) in electrical engineering (EE). The below table creates the said
conditions:

E F G H

NAME DEGREE FIELD EXPERIENCE (years)
MS EE

Let's type the FILTER function in say, cell I2:

=FILTER(A2:D4, B2:B4=F2, C2:C4=G2)

The FILTER function returns the following data:

I J K L

Bob MS EE 2.8

If you want to extract data for a different set of conditions, all you need to do is to simply change the conditions in the condition table.

Do take look at this tutorial, which gives the steps to use the Google Sheets FILTER function with an example.

свернуть