Help needed with array formula

[Johnny Mansoor]

Hi,

I have a 2 dimensional matrix, for comparing various
products. Thus every row is related to A PARTICULAR
product. Every column are the various categories against
which a product is rated. Each category has a weightage
point. Several categories form a group.

Thus, to visualise the spreadsheet; lets take the
following example:

cells D1, E1, F1, etc... are category titles.
cells D2, E2, F2, etc... are the weightage for categories
D1, E1, F1 respectively.

cells A3, A4, A5, onwards are the various products.

For product in cell A3, the ratings against each category
will be found in cells D3, E3, F3, ...

On a separate sheet I mantain a table of groups and
categories; something like:

Group1 CatA
Group1 CatB
Group1 CatC
Group2 CatD
Group3 CatE
Group3 CatF
Group3 CatG
Group4 .....

What I need is the following.

In my 2-d matrix, in column B next to a product I will put
the group name, say Group3. In column C I should have the
weighted rating for that product/group combination. For
example, if I put in cell B3 the data "Group3" then in
cell C3 I should get the value

SUMPRODUCT of (user rating for each category of group3;
weightage of each category of group3)

All ideas helpful. I tried using array-formula which did
not work okay when entered as array-formula in a single
cell; but worked okay when entered as array-formula across
a range of columns. I do not want to bias you with my
solution, and hence I am not publishing it here. I am
certain that your ideas will be more efficient and cooler
than mine.

big thank you.