Allocation of Quantity month & below

[kpi]

Let me describe what I am expecting.

File can be accessed from Link
My ultimate goal is to get the answer in AN column which I have highlighted with green.

Data in AT:AY is simple the pulling the data from other sheet.

The main calculation column required is from AZ columns and to the right side.I have made for 3 months only with manual calculation in Month-1 i.e AZ:BZ column

I have show in AT Column Itemwise with their Sub Total at each.Here,I have taken of Month-1 and done calculation in AZ column whereas BA column is simply AY*AZ.


As I have multiple items with multiple month and data will accordingly shift to right side which I have started from AZ column as manual allocation is not possible in AZ column,so I do expect by vba that allocation of AM column based on month and item be by matching month AU column and item AT and the result towards column AZ & to right.

If any queries then kindly let me know.

 

[C Moore]

Not 100% sure I know what you're asking. Here is an example of a two dimension lookup.

Valuation_Revised.xlsx
	AO
3	Formula
4	175.04
5	730.85
6	186
7	197
Destination-Expected Result

Cell Formulas
Range	Formula
AO4:AO7	AO4	=INDEX($AT$6:$BE$18,MATCH(B4&" Total",$AT$6:$AT$18,0),XMATCH("Month-"&M4,$AT$2:$BE$2,0,-1))

 

[kpi]

Thanx C Moore,

It's not that I am expecting.Refer AZ column,this column data I require which is very much important.
Breakup of AM4 data which is 50.

Similarly breakup of AM5 data which is 213 shown in AZ below Product A.
Because without getting data in AZ column,I cannot get result in BA and AN column.Hence,AZ calculation is very much important.
I don't think with formula it is possible and is possible by VBA only.

Here,also I have presented data of Month 1 only in B:AN column.There will be of course month 2,Month 3,etc.also.whose result shall be in BB,BD and the like.