Embedding index function in sumproduct

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

Could you post the worksheet? I don't really understand where you get the 250s and 109s from...

Do you want to select a row from the other sheet? If so it would need to be the same width as the columns are high, you could use a formula like

=SUM(B1:B4*C1:C4*TRANSPOSE(INDEX('Other sheet'!B2:E5,MATCH("bb",'Other sheet'!A2:A5,0),0)))

confirmed with CTRL+SHIFT+ENTER

I can't post the worksheet because its proprietary information for work. I'll try and make a mock up of it and post that.

To daddylonlegs: I'm not trying to use the entire row, just a single cell chosen from the table.

I'll work on the mockup please stay tuned.

Here is a mockup spreadsheet of what I'm trying to do. Its comlpex but to me it seems like it should be doable on excel

Try

=SUMPRODUCT(--(Sheet2!A8:A12<Sheet3!A6), Sheet2!B8:B12,Sheet2!C8:C12,SUMIF( Sheet1!B1:F1,Sheet2!A8:A12,INDEX(Sheet1!B2:F6, MATCH(Sheet2!A1,Sheet1!A2:A6,0),0)))