How to calculate the value of the cell you are looking for using IF() or some other function that will iterate over the entire array range?

Question

Google Apps Script

Hello. There is a Google table that receives data from other tables using IMPORTRANGE . The same product can be brought to the store several times a day. If I use the IF function and try to calculate the receipt of goods by the name of the goods, then I cannot find all receipts of the goods, but only the first one in the list. Help me write a formula or a script that could iterate through all rows with the same date and find all the results, provided that the goods were imported according to two conditions: store number and product name .
So far, I have only been able to find using the formula only the first receipt of goods by the name of the goods. The date is parsed from other tables as text. It is required to find the value of the receipt price.

=ЕСЛИ(ВПР((to_date(ДАТАЗНАЧ(A2)));'Поступления'!B2:Z;3;0)="Тов1";"I2";
ЕСЛИ(ВПР((to_date(ДАТАЗНАЧ(A2)));'Поступления'!B3:Z;3;0)="Тов1";"I3";
ЕСЛИ(ВПР((to_date(ДАТАЗНАЧ(A2)));'Поступления'!B4:Z;3;0)="Тов1";"I4";
ЕСЛИ(ВПР((to_date(ДАТАЗНАЧ(A2)));'Поступления'!B5:Z;3;0)="Тов1";"I5";
ЕСЛИ(ВПР((to_date(ДАТАЗНАЧ(A2)));'Поступления'!B6:Z;3;0)="Тов1";"I6";
ЕСЛИ(ВПР((to_date(ДАТАЗНАЧ(A2)));'Поступления'!B7:Z;3;0)="Тов1";"I7";
ЕСЛИ(ВПР((to_date(ДАТАЗНАЧ(A2)));'Поступления'!B8:Z;3;0)="Тов1";"i8";
ЕСЛИ(ВПР((to_date(ДАТАЗНАЧ(A2)));'Поступления'!B9:Z;3;0)="Тов1";"i9";
ЕСЛИ(ВПР((to_date(ДАТАЗНАЧ(A2)));'Поступления'!B10:Z;3;0)="Тов1";"i10";
ЕСЛИ(ВПР((to_date(ДАТАЗНАЧ(A2)));'Поступления'!B11:Z;3;0)="Тов1";"i11";
ЕСЛИ(ВПР((to_date(ДАТАЗНАЧ(A2)));'Поступления'!B12:Z;3;0)="Тов1";"i12";"НЕ БЫЛО")))))))))))

Link to table

Answer 1

You can try to solve this using fetch with concatenation

=ARRAYFORMULA(IFERROR(VLOOKUP(
    A2:A&C2:C;
    {
      UNIQUE(A2:A&C2:C)\
      REGEXREPLACE(TRIM(TRANSPOSE(QUERY(
        IF(TRANSPOSE(UNIQUE(A2:A&C2:C))=A2:A&C2:C;B2:B & ", ";"");;ROWS(A2:A))
      ));"(\s*,)+$";"")
    };
    2;
  );""))

Displays a list of stores next to the date and product that received this product on that date.

You can also build a receipts map

Quite a handy report if you add filters to it by products, dates and stores.
Example in Spreadsheet https://docs.google.com/spreadsheets/d/15eAJ-R1i8A...

Answer 2

FILTER ( range; condition1; condition2...) is suitable here to filter out the necessary rows
.