AGGREGATE function of EXCEL into Google Sheets

Question

I have perfect working formula for EXCEL including the AGGREGATE element, but this doesn't work at Google Sheets obviously. I am using German MS Office, but I translate it into English for better understanding:

=IFERROR(INDEX(FORECAST_2021!$J$2:$JN$2;AGGREGATE(15;6;COLUMN(FORECAST_2021!$J$2:$JN$2)-2/((FORECAST_2021!$J$3:$JN$3="BALANCE")*(FORECAST_2021!$A$4:$A$500=B4)*(FORECAST_2021!$J$4:$JN$500=-1));1));"")

This is a pretty solid formula that helps me to bring back a value matching the earlierst date the product is out of stock. So this formula looks up for my product, the first time the stock value is "-1" (I set it like this) and bring back the date (its in the headline) for this "-1" stock value.

I am wondering how I can bring this formula into Google Sheets now.

Here's the Excel original: https://drive.google.com/file/d/1QR-5iJ7eRNqIFStbCC_SLrmobGosz-1M/view?usp=sharing

Anyone any hint?

Best wishes

Answer 1

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