HELP--ARRAY Formula help

[toverstreet]

Hi, I have two tables I am trying to get to multiply against eac
other.

The first has components on one axis and finished product across th
top. The body of the table is the # of components in each finishe
part.

The second has the same list of finished parts down the side and month
across the top. The body is the demand for each part, for each month.

What I need is a table with component and month as axis with the bod
to be finished part demand X component qty for that finished part.

I have attached simplified version showing two tables, and the expecte
answer.

I need some ideas on how to get the table to calculate the answer.
(the actual table is going to be 900rowsX180cols)

Note: I am comfortable with complex formulas and can also write VBA i
you can point me in the right direction

Attachment filename: test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=64188

 

[Peo Sjoblom]

One possible way assuming that the tables are called TABLE1 and TABLE2

=SUM(((OFFSET(TABLE1,,1,1,3)=H5)+(OFFSET(TABLE1,,1,1,3)=H6)+(OFFSET(TABLE1,,1,1,3)=H7))*(OFFSET(TABLE1,MATCH(H12,INDEX(TABLE1,,1),0)-1,1,1,3))*TRANSPOSE(OFFSET(TABLE2,1,MATCH(I11,INDEX(TABLE2,1,),0)-1,3,1)))

entered with ctrl + shift & enter

using fixed references


=SUM(((B44=H5)+(B44=H6)+(B44=H7))*(B55)*(TRANSPOSE(I5:I7)))

both will return 175 for Comp1 and Jan



--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)