Error when using the FILTER function to get the buyer's name, address etc [closed]

Question

I work for an Amazon drop shipping business, and we have a spreadsheet of all the order information, including the order ID, quantity, cost, etc.

I have a different spreadsheet with the buyer information that I need to get onto this sheet. I am trying to use the FILTER function to get the buyer's name, address etc. and because the amount of orders changes every day, I use an open-ended range. For example: FILTER(IMPORTRANGE("link", "sheet!Q2:Q"), IMPORTRANGE("link", "sheet!A2:A") = D2:D). Column A on the other sheet and column D on this sheet are the order ID's, which I am using to get the buyer information.

The problem is that no matter what I do, I get an error like:

FILTER has mismatched range sizes. Expected row count: ____. column count: ____. Actual row count: _____, column count: ____.

I don't know how to fix that because all my ranges are open-ended.

Answer 1

The filter() function requires that condition expressions yield as many rows as are in the first parameter. The formula you quote will error out whenever the number of rows in the imported data and the number of rows in the formula sheet differ. A better way to look up the relevant data by the keys in column D2:D would be to put this formula in row 2 of a free column:

=arrayformula( iferror( vlookup(
  D2:D, 
  importrange("...put spreadsheet ID here...", "sheet!A2:Q")
  17, 
  false 
) ) )

This array formula will fill the whole column in one go, importing the data just once.

The same error message could also result from an importrange() authorization issue. In order to grant the necessary permission to importrange(), you need to unwrap it so that the import gets initiated from outside the filter(), like this:

=importrange("...put spreadsheet ID here...", "A1")

If the new formula cell displays #REF! and a red corner flag, click it to authorize the import. Repeat that with all the imports. After that, your existing formulas that use importrange() should start working as well. You can delete the new formula cells after that.