I'm aware that it's possible to emulate a for loop in Google Sheets via the following:
=ARRAYFORMULA(IF(LEN({range_0 to iterate over}),{function_0 for each element in range_0}))
I've not been able to expand on the above approach to achieve the effect of a multi nested for loop. Is this possible? Ideally this would look something like:
=ARRAYFORMULA(IF(LEN({range_0 to iterate over}),IF(LEN({range_0.1 to iterate over for each parent element in range_0}),{function_0.1 for each element in range_0.1},{OPTIONAL: function_0 for each element in range_0})))
Example application: Iterating over each cell in a row for each row in a column (see below; I'm sure there are ways to achieve this effect via other methods, but I have use for the requested and specified method for cases where a single dynamic formula is necessary to keep up with an unknown and changing number of rows, columns, and functions involved):
Raw data:
| Thing | Type | Feature | Cost |
|---|---|---|---|
| Apple | Fruit | Seeds | $1 |
| Car | Automobile | Wheels | $6,000 |
| Laptop | Computer | Keyboard | $500 |
| Grape | Fruit | Tastiness | $0.50 |
Theoretical formula:
=ARRAYFORMULA(IF(LEN(`Thing`),IF(LEN(COLUMNS({current row of `Thing`})),{current cell in current row of `Thing`} & " of column " & COLUMN({*current cell*}) & " and row " & ROW(`Thing`),""),""))
Output of above theoretical formula:
| Thing | Type | Feature | Cost |
|---|---|---|---|
| Apple of column 1 and row 3 | Fruit of column 2 and row 3 | Seeds of column 3 and row 3 | $1 of column 4 and row 3 |
| Car of column 1 and row 4 | Automobile of column 2 and row 4 | Wheels of column 3 and row 4 | $6,000 of column 4 and row 4 |
| Laptop of column 1 and row 5 | Computer of column 2 and row 5 | Keyboard of column 3 and row 5 | $500 of column 4 and row 5 |
| Grape of column 1 and row 6 | Fruit of column 2 and row 6 | Tastiness of column 3 and row 6 | $0.50 of column 4 and row 6 |
Thank you for your help!
