Need help with shortening formula, with multiple tables as inputs

[mrblister]

	A	B	C	D	E	F
1	MATERIALS REQUIRED PER EACH VEHICLE
2	Vehicle	Nuts	Bolts	PlasticSheets	MetalSheets
3	Bike	100	250	1	0
4	Car	1000	0	2	1
5	Bus	2000	5000	5	0
6	Transport	4000	6000	6	50
7	Plane	10000	20500	20	500
8
9
10	MANUFACTURING OUTPUT
11		Bike	Car	Bus	Transport	Plane
12	Day 1	0	0	0	0	0
13	Day 2	2	2	0	0	0
14	Day 3	0	1	0	1	3
15
16
17	MATERIALS USED PER DAY
18		Nuts	Bolts	PlasticSheets	MetalSheets
19	Day 1	0	0	0	0
20	Day 2	2200	x	x	x
21	Day 3	x	x	x	x

<tbody>
</tbody>

I have 3 tables, on 3 different sheets in my workbook (I put them all in the table above for simplicity).

I need help shortening my formula (For Materials Used Per Day).
In cell B20 (Nuts used on Day 2), I currently have: =(B13*VLOOKUP(B11,A2:E7,MATCH(B18,A2:E2,0),0))+(C13*VLOOKUP(C11,A2:E7,MATCH(B18,A2:E2,0),0))+(D13*VLOOKUP(D11,A2:E7,MATCH(B18,A2:E2,0),0))+(E13*VLOOKUP(E11,A2:E7,MATCH(B18,A2:E2,0),0))+(F13*VLOOKUP(F11,A2:E7,MATCH(B18,A2:E2,0),0))

But my tables are much bigger than my example above! I need to condense my formula.

I would appreciate suggestions for a shorter formula. Thanks in advance!

 

[Eric W]

Given the tables defined as above:

B20: =MMULT($B13:$F13,B$3:B$7)

which is about as simple as you can get! Drag down and across as needed.

This relies on the rows of the first table being the same as the columns of the second table. It also relies on the days in the third table being in exactly the same order as the second table, and the columns of the first table being the same as the columns of the third table. But depending on the actual layout of your sheets, we can still probably make it work using OFFSET and MATCH to get the right ranges.

 

[CalcSux78]

Eric..... Wow!

 

[mrblister]

Given the tables defined as above:

B20: =MMULT($B13:$F13,B$3:B$7)

which is about as simple as you can get! Drag down and across as needed.

This relies on the rows of the first table being the same as the columns of the second table. It also relies on the days in the third table being in exactly the same order as the second table, and the columns of the first table being the same as the columns of the third table. But depending on the actual layout of your sheets, we can still probably make it work using OFFSET and MATCH to get the right ranges.

Thanks Eric! Didn't know about that function. So... simple and clean! It seems to work, and luckily my rows do match my columns. But it fails if a cell has no entry. I put in zeros into the blank cells and it works, but my tables are huge and I don't want to insert zeros every time there isn't a value. Is it possible to get this to work with blank cells (interpreted as zero)?

 

[mrblister]

Thanks Eric! Didn't know about that function. So... simple and clean! It seems to work, and luckily my rows do match my columns. But it fails if a cell has no entry. I put in zeros into the blank cells and it works, but my tables are huge and I don't want to insert zeros every time there isn't a value. Is it possible to get this to work with blank cells (interpreted as zero)?

Oh wait, I found a solution: B20: =MMULT($B13:$F13+0,B$3:B$7+0)

Not my solution. I just figured someone else must've had the same issue so I looked it up.

 

[Eric W]

Glad you got it working! The MMULT (Matrix multiplication) is a little obscure, and many people who forgot their algebra wouldn't think to use it anyway. But every once in a while, it's exactly the right tool for the problem. Good job finding the +0 fix.