Index match, revenue apportionment

[vaxo]

Heello Friends, I need formula with index match or xlookup, in I10:M303 so that, like in yellow cells im workbook please help...

[HansV]

I don't have Microsoft 365, so I don't know what the result is supposed to be. Perhaps in I10:

=$A10*INDEX($K$2:$O$4,MATCH($B10,$I$2:$I$4,0),MATCH(I$9,$K$1:$O$1,0))/1.18-H10

[vaxo]

Thnaks but I need formula like this..

[HansV]

The formula in I10 refers to C10.
The formula in J10 refers to D10.
The formula in K10 refers to E10.
The formula in L10 refers to A10.
The formula in M10 refers to A10.

I don't understand the logic. I would have expected L10 to refer to F10 and M10 to refer to G10. Can you explain?

[vaxo]

Logic is when the payments from C9:G9 is blank in I10:M10 populate nothing when the payments first occurs in this example F10 in L10 populate from the full contract price which is A10 98%. Then as the =A10/1.18*98%= 1,299,121 and the full price 1,564,800/1.18= 1,326,102, so the remaining difference which is 26,981 needs to be populated in 2020 year.

[HansV]

There was no way whatsoever I could have guessed that. Your formulas have no check whether a payment is blank or not, and my crystal ball is out of order.

I still don't understand what the formulas should do, so I cannot help you.