Geek Answers Handbook

Filter range by array

I have a Google spreadsheet showing the EURO 2012 teams and their ratings:

Team Points Goals scored ------ ------ ------------ Germany 6 3 Croatia 3 3 Ireland 0 1 ... ... ...

Now I want to filter this list so that the result contains only a subset of the commands involved. In particular, I want the result list to include only teams from Germany, the Netherlands, Portugal, Italy, England, France, Spain and Croatia.

I know that I can use the FILTER function to extract a single value from a table. So I could write a FILTER expression like =FILTER(A2:C; A2:A = 'Germany' OR A2:A = 'Netherlands' OR A2:A = 'Portugal' OR ...) , but I wanted to avoid this, because the list of commands is kind of dynamic.

So the question is: how can I filter a table using a range of values, not just a single value?

+14
source share
3 answers

For those who are looking for answers that, like me, have stumbled upon this thread, see this page on the Google Product Forum , where both Yogis and Ahab present solutions to the question of how to filter a data range by a different data range.

If A3:C contains the range of UEFA EURO 2012 data to be filtered, and D3:D contains a list of teams that need to be filtered, then E3 ...

 =FILTER(A3:C, MATCH(A3:A, D3:D,0))

or

 =FILTER(A3:C, COUNTIF(D3:D, A3:A))

Positive filter results

And vice versa, if you want to filter by commands not specified 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 is an example spreadsheet I made to demonstrate the effectiveness of these features.

+44
source

See a similar question: SUMIFS function in a Google spreadsheet

+1
source

for those who need to use Greg's formulas and deal with the FILTER range mismatch

 =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 the FILTER formula to return an estimate between two ranges, and the two ranges have different sizes (for example, when they come back from the query) and cannot be resized to fit the same size, and you just got FILTER has mismatched range sizes. Expected row count: etc. FILTER has mismatched range sizes. Expected row count: etc. FILTER has mismatched range sizes. Expected row count: etc. Error then this is a workaround:

for simplicity, let's say your filter ranges: A1: A10 and B1: B8 , you can use the brackets of the array {} to add two virtual lines to the range B1:B8 to match the size of A1:A10 , using REPT where the number is needed Repeats must be calculated by simply calculating between the initial ranges.

then to this REPT formula, we need to add +1 as a correction / fault tolerant (in case the difference between the 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 later we just trim the last row from the range, so that will be B1:B10 versus A1:A10 . we will use 2 unique characters for REPT

The next step is to wrap REPT in SPLIT and divide by the 2nd unique character. then (based on further need) this SPLIT should be enclosed in TRANSPOSE (because we want to match the size of the column with the size of the column), and the last step will be to enclose it in QUERY and again limit output to simple mathematics COUNTA(A1:A10) to crop the last repeat together it will 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)))

+2
source

More articles: