How to structure a vlookup in an arrayformula to accept multiple columns?

Question

I have been working on this problem for a week, where I was looking for the totals of specific row items in a range multiplied by a factor. The formula below achieves the desired outcome. I need help structuring it in an arrayformula to accept multiple columns.

This formula works as is and with an IF statement. =ARRAYFORMULA(IFERROR(VLOOKUP(Sheet4!$A$1:$A$49,'MHC System Material QTY'!$A$16:$N$64,4,0),"")*'MHC System Material QTY'!$D$3)

This gives an error (IFS expects all arguments after 0 to be pairs) =ARRAYFORMULA(IFS('MHC System Material QTY'!$D$3>0,IFERROR(VLOOKUP('Lookup Table '!$A$2:$A$50,'MHC System Material QTY'!$A$16:$N$64,4,0),"")'MHC System Material QTY'!$D$3,'MHC System Material QTY'!$F$3>0,IFERROR(VLOOKUP('Lookup Table '!$A$2:$A$50,'MHC System Material QTY'!$A$16:$N$64,6,0),"")'MHC System Material QTY'!$F$3,""))

The goal is to get the sum total of product line items by row for column D,F,H,J,L,N in MHC System Material QTY tab. Vlookup solves the problem of what items to look for. The sum totals then need to be multiplied by D3,F3,H3,J4,L4,N4 to achieve the sum total fot total order volumes. Column J,L,N are battery option 2 in Lookup table..

Here is the link to sheet. Formula is in column C Total MHC Volume Forecast https://docs.google.com/spreadsheets/d/1etFlYSVPDJXZFOHWZaLFgQ-KDbulV3WWmYfCk0CVvdk/edit?usp=sharing

Your help is appreciated.

Answer 1

Welcome! It seems to me that you are completely unnecessarily complicating the task with additional checks using IF (). In fact, you only need one condition: whether a particular row in 'MHC System Material QTY'!$A$16:$A$72 refers to the corresponding position in 'Lookup Table '. Simply multiply each Cost column (columns E,G,I,K,M,O) by their coefficients ($D$3,$F$3,$H$3,$J$3,$L$3,$N$3), sum it and select the result from the desired row. For example, like this:

=SUMPRODUCT('MHC System Material QTY'!$D$3*'MHC System Material QTY'!$E$16:$E$72
  +'MHC System Material QTY'!$F$3*'MHC System Material QTY'!$G$16:$G$72
  +'MHC System Material QTY'!$H$3*'MHC System Material QTY'!$I$16:$I$72
  +'MHC System Material QTY'!$J$3*'MHC System Material QTY'!$K$16:$K$72
  +'MHC System Material QTY'!$L$3*'MHC System Material QTY'!$M$16:$M$72
  +'MHC System Material QTY'!$N$3*'MHC System Material QTY'!$O$16:$O$72,
'MHC System Material QTY'!$A$16:$A$72='Lookup Table '!A2)

Answer 2

with @JohnSUN help with sumproduct i created this formula which works 100%

=IF(OR('MHC System Material QTY'!$A16='Lookup Table '!$A2,'MHC System Material QTY'!$A16='Lookup Table '!$B2),SUMPRODUCT('MHC System Material QTY'!$D16*'MHC System Material QTY'!$D$3+'MHC System Material QTY'!$F16*'MHC System Material QTY'!$F$3+'MHC System Material QTY'!$H16*'MHC System Material QTY'!$H$3+'MHC System Material QTY'!$J16*'MHC System Material QTY'!$J$4+'MHC System Material QTY'!$L16*'MHC System Material QTY'!$L$4+'MHC System Material QTY'!$N16*'MHC System Material QTY'!$N$4),0)

I now need help putting it into an arrayformula and I not sure how to change the OR operator which does not work in google sheets arrayformula.

Thanks