SUMPRODUCT unequal tables: SUMIF, INDEX, MATCH, LOOKUP ? (1 Viewer)

[lemo]

Hello all, it's been a while since I visited, hope everybody is healthy and in good spirits.

I have two tables, Orders and Prices, see below, I'd like to calculate totals for each row in Orders, by multiplying quantity by price, then summing them.
Currently I do it by creating another table with the help of INDEX and MATCH functions, then SUMPRODUCTing this table with Orders tables.
I'd like to eliminate the extra step of creating a table and write a single formula to do the calculations, but don't seem to have enough brainpower to do it.
Any suggestions?
Thanks!
Excel 2010

Orders	Prod1	Prod2	Prod4	Prod10	Total Cost
Cust1	10	20		5	???
Cust2	5	5	5	5	???
Prices	Prod1	Prod2	Prod3	Prod4	Prod5	Prod6	Prod7	Prod8	Prod9	Prod10
Cust1	$2.00	$2.00	$3.00	$4.00	$2.25			$3.00		$2.25
Cust2	$1.75	$2.00	$2.50	$4.00	$2.00			$3.00		$2.25

 

[Minty]

I could try to explain how to INDEX MATCH MATCH , but this does a much more eloquent job than I ever could;
A possible solution

Or stolen from the same page
SUMPRODUCT(vlookup_column_range = vlookup_value) * (hlookup_row_range = hlookup_value), data_array)

 

[lemo]

Thanks Minty.

Interesting, but I think the ablebits solution applies to single table situations. Once you try to marry two tables of different dimensions, SUMPRODUCT becomes rather unfriendly.

Having said that, I nailed the formula.
For posterity, if you paste the table above into A1, the formula for Total Cost, in F2, will be -

=SUMPRODUCT($B2:$E2, SUMIF($B$5:$K$5,$B$1:$E$1, INDEX($B$6:$K$7,MATCH($A2,$A$6:$A$7,0),) ) )

Took me a while, because I didn't think of using SUMIF to reduce the dimension of the larger table to match smaller one.. Note that it's the second form of INDEX function, Reference.
Was surprised not to be able to find solution online, seems like it's a fairly common task, finding a total cost of customer's order for a subset of all available products (given that product costs vary by customer).

I'll edit the title to include other keywords.

 

[Minty]

Thanks for the update, I'm guessing (with my Access head on) the whole thing would be easier if the table layout was more vertical, rather than a pivot style.