IF(VLook-up maybe?

Sargent12

New Member
Joined
Feb 21, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi I am trying to figure out a formula for calculating the demand for a years worth of material being used in several different parts below is the Tables that should help. I think it should be a IF vlookup but I am still new to formulating using the fuctions for both. Please Help!! Thank you! This is a smaller example, The real data sheet is about 100 rows by 10 columns.
 

Attachments

  • Excel.JPG
    Excel.JPG
    61.3 KB · Views: 9

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Your description is a bit sparse on details, so it isn't clear to me how this is calculated (remember, while the problem may be familiar to you, it is not to us).
Can you please explain the logic for calculating this, and show us the expected results of your example?
 
Upvote 0
Hi, maybe something like this would work for you :

Book1
ABCDEFGH
17Material Demand202320242025202620272028
18Material 130123035
19Material 21560
20Material 31120
21Material 43430
Sheet1
Cell Formulas
RangeFormula
D18,C18:C21D18=SUMPRODUCT($C11:$H11,TRANSPOSE(D$2:D$7))
 
Upvote 0
One option.
Book1
ABCDEFG
1202320242025202620272028
2Part 1525252525252
3Part 2363636363636
4Part 3014500
5Part 4555555
6Part 5121212121212
7Part 6242424242424
8
9Part 1Part 2Part 3Part 4Part 5Part 6
10Material 1252545203022
11Material 23015
12Material 3152040
13Material 44020303030
14
15202320242025202620272028
16Material 13,1883,2333,3683,4133,1883,188
17Material 21,5601,5751,6201,6351,5601,560
18Material 31,1201,1201,1201,1201,1201,120
19Material 43,6703,7003,7903,8203,6703,670
Sheet3
Cell Formulas
RangeFormula
B16:G19B16=BYROW(TRANSPOSE(B2:B7)*$B$10:$G$13,LAMBDA(r,SUM(r)))
Dynamic array formulas.
 
Upvote 0
On second thought, you can also use MMULT.
Book1
ABCDEFG
1202320242025202620272028
2Part 1525252525252
3Part 2363636363636
4Part 3014500
5Part 4555555
6Part 5121212121212
7Part 6242424242424
8
9Part 1Part 2Part 3Part 4Part 5Part 6
10Material 1252545203022
11Material 230015000
12Material 3015020400
13Material 440203030030
14
15202320242025202620272028
16Material 1318832333368341331883188
17Material 2156015751620163515601560
18Material 3112011201120112011201120
19Material 4367037003790382036703670
Sheet2
Cell Formulas
RangeFormula
B16:G19B16=MMULT($B$10:$G$13,B2:B7)
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top