Forum Discussion
another index match?
Here is a simpler version that can be entered in one cell, then filled or copied to the rest.
In C2 on the MA sheet:
=IF($B2="","",INDEX(Plant!$B$2:$M$14,MATCH(INDEX(Source!$A$2:$A$23,MATCH($B2,Source!$C$2:$C$23,0)),Plant!$A$2:$A$14,0),MATCH(C$1,Plant!$B$1:$M$1,0)))
Fill down and to the right.
It mostly works now, so I am super thrilled. Just one thing though...it is picking up twice some line items in rows 16 and 18.
Accounts 3111166 and 311117 have the same type 'Group other sales' on the Source sheet.
The Plant sheet only has type to identify an account, not the name of the account. So the formula returns the values for 'Group other sales' for both accounts.
I don't see how we can avoid that...
Sorry for this, but I think I may have found a "solution", but please advise.
The so-called "solution" is a bit tedious, in that, I would have to qualify all the types, names, etc.
Please see attached, and kindly advise how I can simplify this formula in the MA tab. Thank you!Here is a simpler version that can be entered in one cell, then filled or copied to the rest.
- ok thanks Hans. This should not be so bad anyway as there are only a few line items - I just need to be careful they fall in the right accounts. Thank you!