ARRAYFORMULA(VLOOKUP(QUERY(IMPORTRANGEs))) instead of ARRAYFORMULA(QUERY(IMPORTRANGEs)) doesn't work

Question

I am importing data from 4 sources, their format is:

|       C       | D |     E     | F | G |        H       |
| 31.03.2022    | * | -60 000   | * | * |   Prepayment   |
| 15.01.2022    | * |  17 087   | * | * |    Transfer    |
| 18.06.2022    | * |    -669   | * | * |     Return     |
| 15.06.2022    | * |  170 870  | * | * |     Income     |

Source spreadsheet 1

Result in the destination spreadsheet:

|      A    |    B    |
| Jun 2022  |  170870 |
| May 2022  |       0 |
| Apr 2022  |       0 |
| Mar 2022  |       0 |

In fact, the cells of column A contain 01.06.2022, 01.05.2022, etc.

The formula in B2 selects transactions for the month of June from the sources:

=--IFERROR(query(
  {
    IMPORTRANGE("Id_1", "Operations!$C$2:$H");
    IMPORTRANGE("Id_2", "Operations!$C$2:$H");
    IMPORTRANGE("Id_3", "Operations!$C$2:$H");
    IMPORTRANGE("Id_4", "Operations!$C$2:$H")
  },
    "SELECT sum(Col3) 
      WHERE 
        (Col1 <= date'"&TEXT(EOMONTH($A2,0),"yyyy-mm-dd")&"' 
        AND Col1 >= date'"&TEXT($A2,"yyyy-mm-dd")&"'
        AND Col3 >0 AND Col6 <> 'Transfer' AND Col6 <> 'Loan')
      LABEL sum(Col3) ''",0))

Similar formulas is in other cells of column B.

I want to automatically spread the formula down column B so I don't have to write the formula in every cell.

It is not possible to use ARRAYFORMULA directly. Here I read: "this is because the output of QUERY can be an array itself, so it is not possible to iterate an array output across another array". However, my formula does not output an array of numbers, but a single number, since I use sum(Col3) in it. So that's not the reason.

VLOOKUP does not work either, because column A of the destination spreadsheet contains fixed dates (the beginning of the month), and columns C of the source spreadsheets can contain any days of the month.

How to solve the problem without creating additional sheets or columns?

Answer 1

use:

=ARRAYFORMULA(IFNA(VLOOKUP(MONTH(A2:A); QUERY({IMPORTRANGE("15BFO8u_FbibFj06FI4AsRofvxUuEc4v9gW1s4UM1pcw"; "Operations!C2:H")};
 "select month(Col1)+1,sum(Col3) where Col3 is not null and not Col6 matches 'Transfer|Loan' group by month(Col1)+1"); 2; 0)))