Filter one column based on another column in google sheets

Don’t feel like reading? View our video tutorial for the Google Sheets FILTER function:

Google Sheets has some great functions that can help slice and dice data easily. Although there is an in-built filter feature in Google Sheets, you also have a FILTER function at your disposal.

FILTER function, as the name suggests, will allow you to filter a dataset based on a condition (or multiple conditions).

For example, if you have a list of names with their state names and the sales value, you can use the FILTER function in Google Sheets to quickly get all the records/name one specific state (as shown below).

One benefit of using the FILTER function over the regular filter feature in Google Sheets is that FILTER function results are dynamic. In case you change anything in the original data, the resulting filtered data would automatically update.

This makes the Google Sheets FILTER function is a great choice when creating interactive reports or dashboards.

In this tutorial, I will show you how the FILTER function works in Google Sheets and also cover some useful examples to use it on day-to-day work.

So let’s get started by learning about the syntax of this function.

Google Sheets Filter Function Syntax

the Google Sheet filter formula goes as follows:

FILTER(range, condition1, [condition2, …]):

• range: This is the range of cells that you want to filter.
• condition1: This is the columns/row (corresponding to the column/row of the dataset), that returns an array of TRUEs/FALSES. This needs to be of the same size as that of the range
• [condition2]: This is an optional argument and can be the second condition for which you check in the formula. This again can be a column/row (corresponding to the column/row of the dataset), that returns an array of TRUEs/FALSES. This needs to be of the same size as that of the range.

When you use multiple conditions, those results that return true for both the conditions would be filtered.

In case the FILTER function can not find any result that matches the condition, it would return an #N/A error.

If you’re wondering how this works, go through a couple of examples (listed below) and it will become clear on how to use the FILTER function in Google Sheets.

Example 1 –  Filter Function Google Sheets Based on a Single Condition

Suppose you have the dataset as shown below and you want to quickly filter all the records where the state name is Florida.

The below formula will do this:

The above formula takes the data range as the argument and the condition is B2:B11=“Florida”. This condition checks each cell in the range B2:B11 and if the value is equal to Florida, that record is filtered, else it’s not.

In this example, I have hard-coded the value, but you can also have this value in a cell and then refer to this cell. For example, if you have the text Florida in cell H1, you can also use the below formula:

A few things to know about the FILTER function.

The FILTER function in Google Sheets returns an array of values that spill over the adjacent cells (this is called a dynamic array). For this to work, you need to make sure that the adjacent cells (where the results would be placed) should be empty.

If any of the cell(s) is not empty, your formula will return a #REF! error. Google Sheets also tells you why it’s giving an error by showing a red triangle at the top-right of the cell and when you hover over it, it will show a message:

Array result was not expanded because it would overwrite data in F3

And as soon as you delete the filled cell that prevents the FILTER function to give the result, it will automatically fill the range with the result.

Also, the result of the FILTER formula is an array and you can change a part of the array. This means that you cant not change or delete one cell (or couple cells) in the result. You will have to delete the entire formula result. To delete the result, you can select the cell where you entered the formula and then hit the delete key.

Example 2 – Filter Google Sheets Based on Multiple Conditions (And Condition)

You can also use the FILTER function to check for multiple conditions in such a way that it only returns those records where both the conditions are met.

For example, suppose you have the below data set and you want to filter all the records where the state is Florida and the sale value is more than 5000.

You can do this using the below formula;

The above formula checks for two conditions (where the state is Florida and sale value is more than 5000) and returns all the records that meet these criteria.

Similarly, if you want, you can have multiple conditions in the same FILTER formula.

Example 3 – Filter Records Based on Multiple Conditions (OR Condition)

In the above example, I have checked for two conditions and return results where both the conditions are TRUE.

You can also check for OR condition in the FILTER formula.

For example, suppose you have the dataset as shown below and you want to get all the records for California and Iowa. This means that the condition should be the state is either California or Iowa (which makes this an OR condition).

The below formula will do this:

The above formula uses the addition operator in the condition to first check both the conditions and then add the result of each. Since these conditions return an array or TRUEs and FALSEs, you can add these (since a TRUE is 1 and FALSE is 0 in Google Sheets).

This will give you 0 (or FALSE) where both the conditions are not met, 1 where one of the two conditions are met, and 2 where both the conditions are met.

And then the FILTER formula will return all the records where the conditions return value more than 0.

Example 4 – How to Filter In Google Sheets for  Top 3 or Top 5 Records

You can also use the FILTER function to quickly get the top 3 or top 5 (or whatever number of top/bottom number of records you choose).

For example, suppose I have the dataset as shown below and I want to quickly get the records for the top 3 sales values.

The below formula will do this:

The above formula uses the LARGE function to get the third largest value in the dataset. This value is then used in the condition to check whether the values in column C are greater than or equal to this value or not.

This would return all the records that match the criteria, which would be the top three records.

In case you want to get the bottom three records, you can use the below FILTER formula:

Example 5 – How to Use Filter Function in Google Sheets to SORT the Filtered Data

So far, all the filtering function examples that we have seen would filter the data in the same order in which it occurs in the dataset.

But what if you want to get the sorted dataset.

For example, suppose you’re filtering the top 5 records, it would be more useful to have these sorted in descending order (largest at the top).

Below is the formula that will filter the data and show it in descending order:

The above formula uses the same FILTER formula we used in the previous example to fetch the top three records based on the sales value.

And since I wanted the final result to be sorted in descending order, I have wrapped the FILTER function within the SORT function. The SORT function uses the result given by the FILTER formula and sorts it based on the third column (which has the sales value).

The third argument in the SORT function is FALSE, which is to specify that I want the final result in descending order. In case you leave it blank (or make it TRUE), the result will be in ascending order.

Example 6 – Spreadsheet Filter All Even Number Records (Or Odd Number Records)

This is not such a common use-case, but it’s something I have had to do when I get my data from someone else or from a database or webpages.

Many times, the data you need will only be in alternate rows (or every third/fourth/fifth row), and you would have a need to get rid of the extra rows so that you can get all the useful data together.

In such a case, you can use the FILTER function to quickly filter and get all the even number rows together (or all odd-numbered rows together). And you can also modify the formula to filter every third, fourth, or nth row in Google Sheets.

Suppose you have a dataset as shown below and you want to filter all the even-numbered rows in this dataset.

Below is the formula that will filter all the even rows:

The above formula uses the ROW function to get the row numbers of all the rows in the dataset. It then subtracts 1 from it as our dataset starts from the second row onwards.

Now, it uses the MOD function to check the following condition – MOD(ROW(A2:A11)–1,2)=0)

This would return TRUE for all the even-numbered rows and FALSE for all odd-numbered rows. And this array of TRUE and FALSE is used by the FILTER function to extract the records.

Similarly, if you want only the ODD numbered records, you can use the below formula:

And in case you want to filter every third row, you can use the below formula in Google Sheets:

Google Sheets Filter Function FAQ

How Do I Filter Top 10 Values in Google Sheets?

Check out example 4 in our guide above, it will show you how to filter by top numbers no matter how many you decide to incorporate

What Does the FILTER Function Do?

The FILTER function makes it easy to filter out any unimportant data from your spreadsheet so you only see what is needed for your other functions or calculations.

Can I Do a Filter of a Filter?

Technically yes, but you’d be much better off using the VLOOKUP function instead. It’s a much more efficient way of doing the same thing.

Can I Reference a Criteria Cell With the Filter Function in Google Sheets?

Yes, you just have to use the cell reference in the condition section of the FILTER function syntax.

So these are some of the examples where the Google Sheets FILTER function can be a real time saver. And when you combine it with other formulas, you can get a lot of stuff done with it.

Hope you found this tutorial useful.

Other Google Sheets tutorials you may like: