You want to select the first balance with a value of "-1", and then identify the date on which this applies.
There are five columns for each date "IMPORT, GROWTH, EST VELO, EST SALES, BALANCE", each set represents one week, the entire set represents one year. This means that there are 53 identical sets of column names.
Formula to identify 1st Balance=-1
=ifna(address(3,(match(B5,query({Tabelle2!A3:JG3},"Select "&ArrayFormula(join(", ",text(query(transpose({column(Tabelle2!G3:JG3);mod(column(Tabelle2!G3:JG3)-column(Tabelle2!G3),5)}),"Select Col1 where Col2=0"),"Col0")))),0)*5)+3-1,4,1,"Tabelle2"),"no match")
Formula to identify date
=indirect(address(3-2,(match(B5,query({Tabelle2!A3:JG3},"Select "&ArrayFormula(join(", ",text(query(transpose({column(Tabelle2!G3:JG3);mod(column(Tabelle2!G3:JG3)-column(Tabelle2!G3),5)}),"Select Col1 where Col2=0"),"Col0")))),0)*5)+3-1,4,1,"Tabelle2"),1)
LOGIC: 1ST BALANCE
query(...)
: this is a routine documented by Infoinspired that allows every nth column to be selected; in this case, the formula was configured to select every fifth ("Balance") column. The column number is identified as a value (in this case 5).
- `match(-1, query(...)): delivers the index number for the first column with a balance of -1. Note, the index is multiplied by 5 in order to
address(...)
: the row is known (3), the column index (18) is known and the actual column is determined by first multiplying by 5 (to allow for sets of 5 columns), then adding +3-1 (to adjust for the range start = A but the first column of each set = Column C (3).
ifna
: in the event that no balance equals -1.
LOGIC: DATE
indirect()
: take the formula to derive the address of the balance (CN3) and adjust the row number by -2 to get the valance un row #1.
Credit
Infoinspired: Dynamic Formula to Select Every nth Column in Query in Google Sheets