How to improve array formula for bigger data sets?

Question

In range A1:C4, I have some data arranged as shown below,

	Yr1	Yr2
Apples	9	5
Bananas	4	3
Oranges	1	4

For a combination of elements given in range A6:B6 I am looking for a formula that will evaluate the sum of the elements in each column and give me the maximum result.

Example: A6:B6 = {Bananas; Oranges}, the formula should evaluate: Max({0;1;1} * {9;4;1};{0;1;1} * {5;3;4}) = Max(5;7) = 7

I believe this should be feasible with array formulas, but I don't know how to get the intermediate array results. So far I have come up with this, where I repeat the same formulas for each new column:

=MAX(SUM((B2:B3)*ISNUMBER(MATCH($A2:$A3;$A6:$B6;0)));SUM((C2:C3)*ISNUMBER(MATCH($A2:$A3;$A6:$B6;0))))

but it becomes too cumbersome as soon as the number of columns grow. Could anyone point a more practical solution for bigger data sets?

Thank you!

Answer 1

You do not need to split it all up like you do:

=MAX(ISNUMBER(MATCH($A$2:$A$4,$A$6:$B$6,0))*(B2:B4+C2:C4))

Depending on one's version this may need to use Ctrl-Shift-Enter instead of Enter when exiting edit mode.

If the concern that the number of columns will increase and we do not want to keep adding +D2:D4 and so on we can use MMULT:

=MAX(MMULT((B2:C4)*(ISNUMBER(MATCH(A2:A4,A6:B6,0))),TRANSPOSE(COLUMN(B2:C4)^0)))

Now all the Yr columns are a single reference and can be expanded more easily.

Depending on one's version this may need to use Ctrl-Shift-Enter instead of Enter when exiting edit mode.