Lookup and multiple column calculation

[Mick S]

I work at a manufacturing company where we have standard cost. In order to value inventory correctly on the balance sheet we have to capitalize our standard cost variances based on a turnover ratio. I have a few variables:

1. Cost Variances
- change each month
- can be positive or negative
2. Months to Capitalize
- change each month and based off of turnover ratio
- if more than 12 months just defaults to 12
- based off of major product line

See attached.

Attempting to take cost variances (tab 1), determine based on major product line what the months to capitalize are (tab 2), then calculate the correct balance and then return that value on a tab 3 with a formula that is flexible enough to be copied and pasted into the entire model.

Part that I am really struggling with is the partial month to capitalize.

Model is much simpler than reality. I have 10 major product groups each with about 10 base level product lines.