Google Sheets FILTER function based on cell value
|
The Google Sheets Filter function is a powerful function we can use to filter our data. The Google Sheets Filter function will take your dataset and return (i.e. show you) only the rows of data that meet the criteria you specify (e.g. just rows corresponding to Customer A). Show
Suppose we want to retrieve all values above a certain threshold? Or values that were greater than average? Or all even, or odd, values? The Google Sheets Filter function can easily do all of these, and more, with a single formula. This video is lesson 13 of 30 from my free Google Sheets course: Advanced Formulas 30 Day Challenge. In this example, we have a range of values in column A and we want to extract specific values from that range, for example the numbers that are greater than average, or only the even numbers. The filter formula will return only the values that satisfy the conditions we set. It takes two arguments, firstly the full range of values we want to filter and secondly the conditions we’re going to apply. The syntax is:
where Condition 2 onwards are all optional i.e. the Filter function only requires 1 condition to test but can accept more. How do I use the Filter function in Google Sheets? For example in the image above, here are the conditions and corresponding formulas:
The results are as follows: (Note: not all the values are shown in column A.) Can I test multiple conditions inside a Google Sheets FILTER function?Absolutely! For example, using the basic data above, we could display all the 200-values (i.e. values between 200 and 300) with this formula:
Can I test multiple columns in a Filter function?Yes, simply add them as additional criteria to test. For example in the following image there are two columns of exam scores. The Filter function used returns all the rows where the score is over 50 in both columns: The formula is:
Note, using the Filter function with multiple columns like this demonstrates how to use AND logic with the Filter function. Show me all the data where criteria 1 AND criteria 2 (AND criteria 3...) are true. For OR logic, have a read of this post: Advanced Filter Examples in Google Sheets Can I reference a criteria cell with the Filter function in Google Sheets?Instead of hard-coding a value in the criteria, you can simply reference another cell which contains the test criteria. That way you can easily change the test criteria or use other parts of your spreadsheet analysis to drive the Filter function. For example, in this image the Filter function looks to cell E1 for the test criteria, in this case 70, and returns all the values that exceed that score, i.e. everything over 70. The formula in this example is:
Can I do a filter of a filter?Yes, you can! Use the output of your first filter as the range argument of your second filter, like this:
ResourcesAdvanced Filter Examples in Google Sheets Google documentation for the FILTER function. Related ArticlesHow to use an advanced filter with an OR condition in Google Sheets. Knowing the trick will save you hours, so you can filter multiple conditions easily. Is there advanced filter in Google Sheets?You can also set up advanced filters that filter data by specific conditions. For example, you can filter data so that only values that are greater than a certain amount will appear. Click the filter arrow for a column. Click Filter by condition.
Is there an Excel function that works the same as filter () from Google Sheets?So, the short answer is no there is no function in Excel that would exactly match the use of Filter in Sheets.
Does Google Sheets have Xlookup function?XLOOKUP for google sheets is a custom function that comes handy when you want to search for things from a table or range using another row.
How filter formula works in Google Sheets?FILTER can only be used to filter rows or columns at one time. In order to filter both rows and columns, use the return value of one FILTER function as range in another. If FILTER finds no values which satisfy the provided conditions, #N/A will be returned.
|
