Please help allocate cost by department....

[vaxo]

hello, need to allocate cost by department using match index or vlookup... in the yellow range with formulas ..

 

[Hui]

C5: =IFERROR($B5*(OFFSET(C$2,MATCH($H5,$A$3:$A$14,0),)/OFFSET($B$2,MATCH($H5,$A$3:$A$14,0),)),0)

Copy across and down

A14 has a trailing space which you need to remove

Refer attached file:

 

[trprasad78]

Hi

Paste below formula in C5 and drag it working fine.
check the attached file.
=(C$14/$B$14)*$B5

 

[AVK]

Cost allocated based on other account.
Plz refer attach file. In this case you can use simple sum formula.

 

[vaxo]

Thanks, Hui and Avk great solutionss ...

 

[vaxo]

Can you help in this case ?? like previous one but i cannot know how to solve..

 

[Hui]

B21: =INDEX($B$5:$B$10,MATCH($A21,$A$5:$A$10,0))*OFFSET(B$13,MATCH($G21,$A$14:$A$16,0),)/OFFSET($F$13,MATCH($G21,$A$14:$A$16,0),)

 

[vaxo]

Thanks brilliant ..

 

[AVK]

plz refer attach file.

 

[r2c2]

One more formula... (paste this in B21) and copy.

=VLOOKUP($G21,$A$14:$F$16,COLUMNS($B$20:B$20)+1,FALSE)/VLOOKUP($G21,$A$14:$F$16,6,FALSE)*$B5

For total column, simply addup B21:E21 and drag down.

 

[John Jairo V]

Hi!

Another way (just for fun). Put it B21 this array formula (CSE):

=PRODUCT($B5,VLOOKUP($G21,$A$14:$F$16,CHOOSE({1,2},COLUMNS($A21:B21),6),)^{1,-1})

And drag it down and right. Blessings!