# Need help with shortening formula, with multiple tables as inputs

#### mrblister

##### Board Regular
 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!

Last edited:

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### Eric W

##### MrExcel MVP
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.

Last edited:

Eric..... Wow!

#### mrblister

##### Board Regular
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

##### Board Regular
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

##### MrExcel MVP
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.

Replies
2
Views
235
Replies
0
Views
428
Replies
1
Views
405
Replies
0
Views
358
Replies
1
Views
1K

1,191,703
Messages
5,988,179
Members
440,136
Latest member
dandanfielding

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back