Array math problem

Hi Numbers Wizards,

I'm looking to perform a function that returns the product of two arrays. It's complicated by the fact that one of the arrays is the result of a bunch of queries to a look up table based on the inputs of a number of drop-down menus.

In excel, I could simply use the lookup function to return an array, and insert that into a sumproduct function as below.

=SUMPRODUCT(VLOOKUP(A1:A10,C1:F5,3,FALSE),B1:B10)

In which my drop-down options are laid out in column A, the value table corresponding to those options is laid out in the third column of this lookup table C1:F5, and the multiplier is laid out in column B.

This doesn't work in Numbers because the vlookup function doesn't seem to return an array as I'd hope it would given two array inputs as excel does.

Anyone have any suggestions how to make this work in Numbers?!

Thank you!