28

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?

player0
  • 124,011
  • 12
  • 67
  • 124
Vidar S. Ramdal
  • 1,164
  • 1
  • 14
  • 38

4 Answers4

69

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

Positive filter results

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

Negative filter results

Here's an example spreadsheet I've made to demonstrate these functions' effectiveness.

Vidar S. Ramdal
  • 1,164
  • 1
  • 14
  • 38
Greg
  • 1,264
  • 14
  • 21
  • 2
    I would like to note, that you should include the actual details in your answer, as links can often be broken, making this answer not so useful in the future. – Douglas Gaskell Jun 07 '16 at 17:28
  • 2
    Thank you, Douglas. You're absolutely right. I hope my updated answer will suffice. – Greg Jun 11 '16 at 22:16
5

for those who need to use greg's formulas and struggle with range mismatch of FILTER

=FILTER(A1:A, MATCH(A1:A, B1:B, 0))

=FILTER(A1:A, COUNTIF(B1:B, A1:A))

=FILTER(A1:A, ISNA(MATCH(A1:A, B1:B, 0)))

=FILTER(A1:A, NOT(COUNTIF(B1:B, A1:A)))

in case you need to use FILTER formula to return evaluation between two ranges, and those two ranges are of different size (like when they are returned from a query) and can't be altered to match the same size and you just got FILTER has mismatched range sizes. Expected row count: etc. error, then this is a workaround:

to keep it simple let's say your ranges for the filter are A1:A10 and B1:B8, you can use array brackets {} to append two virtual rows on range B1:B8 to match size A1:A10 by using REPTwhere number of needed repetitions shall be calculated by a simple calculation between initial ranges.

then to this REPT formula, we need to add +1 as a correction/failsafe (in a case the difference between two initial ranges is 1), because REPT works with a minimum of 2 repetitions. so in a sense, we will need to create a range of B1:B11 (from B1:B8) and laters we will just trim off the last row from a range so it would be B1:B10 against A1:A10. we will use 2 unique symbols for REPT

next step would be to wrap REPT into SPLIT and divide by 2nd unique symbol. then, (based on a further need) this SPLIT needs to be wrapped into TRANSPOSE (because we want to match column size to column size) and the last step would be to wrap it into QUERY and limit out the output again by simple math of COUNTA(A1:A10) to trim off the last rept. put together it would look like this:

=FILTER(A1:A10, NOT(COUNTIF(QUERY({B1:B8; 
 TRANSPOSE(SPLIT(REPT("♂♀", COUNTA(A1:A10)-COUNTA(B1:B8)+1), "♀"))}, 
 "limit "&COUNTA(A1:A10), 0), A1:A10)))

Community
  • 1
  • 1
player0
  • 124,011
  • 12
  • 67
  • 124
0

Some possible and different approaches:

=FILTER(C5:C;REGEXMATCH(C5:C;"one|two|five"))

result: any text containing one, two, five. Case sensitive.

=FILTER(C5:C;REGEXMATCH(C5:C;JOIN("|";DC5:DC7)))

same as previous but values are in list: DC5:DC7

=FILTER(C5:C;REGEXMATCH(C5:C;"^("&JOIN("|";EE5:EE7)&")$"))

same as previous + match whole text

Suggested earlier options:

=FILTER(C5:C;MATCH(C5:C;EG5:EG7;))

=FILTER(C5:C;countif(EK5:EK7;C5:C))

Case insensitive result.

Same, not in list

=FILTER(C5:C;ISNA(MATCH(C5:C;EF5:EF7;)))

Interesting: gives the difference between lists.

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
0

This is player0 improved answer for when you need to use mismatched table sizes

The formula filters a whole table based on the list in another column. I used a lambda function so you can easily just change the last three parameters to check if this will work for you.

argument 1st - a range of a table to be filtered (or a query result etc.)

argument 2nd - column number in the filtered table to check the matching list against

argument 3rd - the list of the things the specified column should contain

=lambda(table_to_filter, no_of_col_to_filter_by, list_to_match, FILTER(table_to_filter, COUNTIF(QUERY({list_to_match; 
 TRANSPOSE(SPLIT(REPT("♂♀", COUNTA(index(table_to_filter,,no_of_col_to_filter_by))-COUNTA(list_to_match)+1), "♀"))}, 
 "limit "&COUNTA(index(table_to_filter,,no_of_col_to_filter_by)), 0), index(table_to_filter,,no_of_col_to_filter_by))))(A1:C8, 1, G1:G)
Kamil Chłosta
  • 1
  • 1