INDEX return multiple columns

[candy.chiu.ad]

I have the following data

Range1 (1x4)
id 1 3 5

Range2 (3x1)
2
4
6

The goal is very simple. I want to extract the numbers in Range1, then sum product them with Range2.

To extract numbers - Index(Range1, 1, {2,3,4})
Then multiply - MMULT(Index(Range1, 1, {2,3,4}), Range2)

The problem is since the result is single-cell, even I instructed cell to use an array formula, it defaulted back to regular formula. As a result, Index(Range1, 1, {2,3,4}) returned a 1x1 array instead of a 1x3 array, causing MMULT to fail.

Because of this, TRANSPOSE + SUMPRODUCT didn't work neither.

I then thought about creating an IndexWrapper function in VBA to force it to return an array.

' As a Test
Public Function IndexWrapper (arr As range) As Variant()

Dim cols(3) As Double
cols(1) = 2
cols(2) = 3
cols(3) = 4

IndexWrapper = WorksheetFunction.Index(arr, 1, cols)

End Function

WorksheetFunction.Index doesn't seem to take the cols into consideration. The entire row is returned.

Anyone has a good solution for this problem? I am trying not to create an one off work around. Thanks.

 

[Dave Cromley]

I know I'm probably missing the problem here, but
B11 has 1,3,5
A2:A4 has 2,4,6
then D4 has =MMULT(B11,A2:A4)
which gives 44. From here, whither?