When a formula doesn't work it should be simplified to make it easier to find where the error is occurring.
In the case exposed by the OP, the
IMPORTRANGE parts could be moved out of the formula, like moving to another part of the spreadsheet.
In this answer use the following names are used to make the formulas more readable.
- Headers is a 1 X 3 range.
- Data1 is the data from returned by
IMPORTRANGE("URL1","A2:C5"). This have three columns and three rows.
- Data2 is the data returned by
IMPORTRANGE("URL2","A2:C5"). This have three columns and three rows.
The simplified version of the formula exposed by the OP is
The error reported by the OP occurs because the second argument of FILTER should be an array of one column (n X 1, where n is the number of rows).
One way to resolve the problem is to concatenate the columns and for that in this answer the proposal is to use the following construct:
The above construct takes advantage of feature of
QUERY that concatenates the rows declared as headers. The first inner
Transpose makes the rows becomes columns so they are treated as headers. The second argument is empty and the third argument is a number bigger enough to empconpases all the rows, in this case all the columns of Data1. The outer
Transpose makes the result of
QUERY becomes an array of one column.
Using a comparator like
<> in an array formula compares row by row, but assuming that SHEET1 and SHEET2 could have different number of rows could be better to use
ISNUMBER will return
TRUE when a math was found and
FALSE otherwise. As it will return a single result for each cell when an array is used as argument, so in this case it will return an 3 X 1 array.
The proposed formula in the short answer section is easily adapted to source arrays of any number of columns and rows. The requirement is that SHEET1 and SHEET2 have the same number of columns.