=C5+(IF(INDEX(ColumnD, 1)="apples",IF($B3<index(table1, match("fertilizernumber",="" table1rownames,0),="" match(index(columna,="" 1)&"apples",="" table1headers,0)),vlookup($b3,table2,match(index(columna,="" 1)&"apples",table2headers,0),true),seriessum($b3,1,1,indirect(vlookup(index(columna,="" 1),table3,match("coefs",table3headers,0))))),0)*lookup(index(columna,="" 1),$a$2:$z$2,$a5:$z5))+<="" td=""></index(table1,> |
(IF(INDEX(ColumnD, 2)="apples",IF($B3<index(table1, match("fertilizernumber",="" table1rownames,0),="" match(index(columna,="" 2)&"apples",="" table1headers,0)),vlookup($b3,table2,match(index(columna,="" 2)&"apples",table2headers,0),true),seriessum($b3,1,1,indirect(vlookup(index(columna,="" 2),table3,match("coefs",table3headers,0))))),0)*lookup(index(columna,="" 2),$a$2:$z$2,$a5:$z5))+...<="" td=""></index(table1,> |
(IF(INDEX(ColumnD, n)="apples",IF($B3<index(table1, match("fertilizernumber",="" table1rownames,0),="" match(index(columna,="" n)&"apples",="" table1headers,0)),vlookup($b3,table2,match(index(columna,="" n)&"apples",table2headers,0),true),seriessum($b3,1,1,indirect(vlookup(index(columna,="" n),table3,match("coefs",table3headers,0))))),0)*lookup(index(columna,="" n),$a$2:$z$2,$a5:$z5))<="" td=""></index(table1,> |
<tbody>
</tbody>
EDITING!!! I DON'T KNOW WHY, BUT IT WON'T DISPLAY THE WHOLE FORMULA!
n = number of entries in ColumnD (the same number of entries in columnA)
The above formula is located in C6, copied down. It checks the each item (fruit type) in ColumnD, and if it matches "apples", the rest of the function is computed. If ColumnD is a match, the value of ColumnA (on the same row as ColumnD) is returned (like a left-lookup) as the identifier (fruit name e.g. "RoyalGala") used in the rest of the formula, as the ID used to searched for in the other tables. If it matches certain criteria, look up the output on Table2, otherwise compute the output using a formula.
Then after all the results are summed up, then add the previous amount from the cell above.
Any ideas on ways to shorten this? It works, but is super-long. I tried converting it to array and sumproduct but I must've made a mistake somewhere.
Last edited: