Index match, revenue apportionment
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Index match, revenue apportionment
Heello Friends, I need formula with index match or xlookup, in I10:M303 so that, like in yellow cells im workbook please help...
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Index match, revenue apportionment
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
=$A10*INDEX($K$2:$O$4,MATCH($B10,$I$2:$I$4,0),MATCH(I$9,$K$1:$O$1,0))/1.18-H10
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: Index match, revenue apportionment
Thnaks but I need formula like this..
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Index match, revenue apportionment
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?
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?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: Index match, revenue apportionment
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.
-
- Administrator
- Posts: 78558
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Index match, revenue apportionment
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.
I still don't understand what the formulas should do, so I cannot help you.
Best wishes,
Hans
Hans