I might be biting off more than I can chew with this, but here's what I'm trying to get excel to do:

I've got one worksheet (call it 'Other Sheet') that essentially serves as a table. It has a header row on the top, a column of names for the rows on the left, and data in the middle. It kind of looks like the following:

a b c d e f...
aa 2 4 5 6 7 2...
bb 3 6 7 2 1 0...
cc 9 6 1 4 5 3....
.
.
.

On another worksheet, I've got some rows that look like this:

b 250 109
b 250 58
e 250 47
a 250 84

I am trying to fill one cell with the value of a sumproduct. The sumproduct will multiply three arrays together. The first two are the columns with 250s and 109 and below. For the final column, i want to create an by picking the value out of the table using the first column of the row and then a single cell somehwere else on the sheet with value "bb". If you let the 2nd and 3rd column be named B and C respectively, I did the following:

SUMPRODUCT(B1:B4*C1:C4*INDEX('Other Sheet'!B2:G5,MATCH("bb",'Other Sheet'!A2:A5,0),MATCH(A1:A4,'Other Sheet'!B1:G1,0)))

I've been working on this for over a day, and can get it to work partially but not completely. Essentially, for the third thing to multiply by, I want to pass it array that I've made using the index function. However, I don't want to create the array in cells on the spreadsheet (I have to do this a bunch of times) but just want to pass it directly into the function. How can I get this to work?

Thanks so much,

Ryan