Menu
I have a Google Spreadsheet containing the teams of the UEFA EURO 2012, and their scores:
Team Points Goals scored ------ ------ ------------ Germany 6 3 Croatia 3 3 Ireland 0 1 ... ... ... Now I want to filter that list, so that the result contains only a subset of the teams involved. Specifically, I want the resulting list to contain only the teams Germany, Netherlands, Portugal, Italy, England, France, Spain and Croatia.
I know I can use the FILTER function to extract a single value from the table. Thus, I could probably write a FILTER expression like =FILTER(A2:C; A2:A = 'Germany' OR A2:A = 'Netherlands' OR A2:A = 'Portugal' OR ...) but I would like to avoid this, as the list of teams is sort of dynamic.
So the question is: How can I filter the table by a range of values - not just a single value?
943
To filter a data in an array formula (to exclude or require certain values), you can use an array formula based on the IF, MATCH, and ISNUMBER functions. where "data" is the named range B4:D11 and "filter" is the named range F4:F6. Note: this is an array formula and must be entered with control + shift + enter.
JavaScript Array filter()The filter() method creates a new array filled with elements that pass a test provided by a function. The filter() method does not execute the function for empty elements. The filter() method does not change the original array.
Using filter() on an Array of Numbers The item argument is a reference to the current element in the array as filter() checks it against the condition . This is useful for accessing properties, in the case of objects. If the current item passes the condition , it gets returned to the new array.
For answer-seekers who stumble onto this thread as I did, see this Google product forum page, where both Yogi and ahab present solutions to the question of how to filter a range of data by another range of data.
If A3:C contains the range of UEFA EURO 2012 data to be filtered, and D3:D contains the list of teams by which to filter, then E3 ...
=FILTER(A3:C, MATCH(A3:A, D3:D,0)) or
=FILTER(A3:C, COUNTIF(D3:D, A3:A)) 
Conversely, if you'd like to filter by teams not listed in D3:D, then E3...
=FILTER(A3:C, ISNA(MATCH(A3:A, D3:D,0))) or
=FILTER(A3:C, NOT(COUNTIF(D3:D, A3:A))) 
Here's an example spreadsheet I've made to demonstrate these functions' effectiveness.
198
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
