Google-sheets – FILTER has mismatched range size; referencing the second last cell in a column in range

filterformulasgoogle sheetsimportrange

I am using a multicriteria filter to check if my sheet contains a certain value combination. The formula should automatically include any new rows added to the sheet. However, if I do it this way: 

=IF(FILTER(IMPORTRANGE(Overview!V22, "TabName!D2:F"), 
           IMPORTRANGE(Overview!V22, "TabName!D2:D") = "KI", 
  ISNUMBER(IMPORTRANGE(Overview!V22, "TabName!F2:F"))) = "KI", "KI", "")

This is the error I get:

FILTER has mismatched range sizes. Expected row count: 162. column count: 1. Actual row count: 1, column count: 1.

I think the problem is that for the conditions I need to define the range, e.g. F2 to the second last cell in a column but I don't know how to do that within a range definition.

I am really new to this and would appreciate any hints 🙂

Best Answer

example for those who need to use FILTER 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 cell. 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)))

Related Topic