ranjith2523
Board Regular
- Joined
- Apr 29, 2014
- Messages
- 137
- Office Version
- 365
Hi Friends,
I am trying to perform a validation and again, I need experts to help with Index Match Formula. Let me clearly explain the steps.
In a workbook, I have a sheet called "ZVREP76_PPC" in which I have data as mentioned below.
I have another sheet as "PPC_Val" in which I am trying to apply the validation formula.
Here I need to apply the formula in Column L, I have tried something like "=INDEX(A:A&ZVREP76_PPC!B:B,MATCH(PPC_Val!C2,ZVREP76_PPC!B:G,0))" but it doesn't work for me.
Criteria
The expectation is from the sheet "PPC_Val" we need to ensure that the Material "P26865-B21" and the Div/PL "SI" is matched in the sheet "ZVREP76_PPC" from Column B and Column G.
Condition: In ZVREP76_PPC tab If the combination of "P26865-B21" and "SI" should match as per "PPC_Val" tab Column A and Column C. Also, we need to ensure that in "ZVREP76_PPC" tab that the combination of "P26865-B21" and "SI" is repeated for 71 times.
If the above condition is met then the result should appear as "Passed" in Cell K2 in "PPC_Val" tab or else it should be failed.
Example
I hope I have clearly explained the requirement and thank you for all your help in advance.
Regards,
Ranjith
I am trying to perform a validation and again, I need experts to help with Index Match Formula. Let me clearly explain the steps.
In a workbook, I have a sheet called "ZVREP76_PPC" in which I have data as mentioned below.
Column A (Blank) | Material _ Column B | Column C (Blank) | MATERIAL TYPE_ Column D | PLANT_ Column E | SALES ORG_ Column F | DIVISION_ Column G | |
P26865-B21 | FERT | 8900 | JP00 | SI | |||
P26865-B21 | FERT | E500 | KR00 | SI | |||
P26865-B21 | FERT | CJ00 | KZ00 | SI | |||
P26865-B21 | FERT | BZ00 | LT00 | SI | |||
P26865-B21 | FERT | JM00 | LU30 | SI |
I have another sheet as "PPC_Val" in which I am trying to apply the validation formula.
Material _ COL A | Description _ COL B | Div/PL _ COL C | MG4_ COL D | Support Prod. Line_ COL E | Business Code_ COL F | Prod. Cat_ COL G | Material_ COL H | S.Org_ COL I | Plant_ COL J | Div/PL_ COL K | MG4_ COL L |
P26865-B21 | SI | HW | 300 | J3M8 | J | Passed | Passed | Passed | Passed |
Here I need to apply the formula in Column L, I have tried something like "=INDEX(A:A&ZVREP76_PPC!B:B,MATCH(PPC_Val!C2,ZVREP76_PPC!B:G,0))" but it doesn't work for me.
Criteria
The expectation is from the sheet "PPC_Val" we need to ensure that the Material "P26865-B21" and the Div/PL "SI" is matched in the sheet "ZVREP76_PPC" from Column B and Column G.
Condition: In ZVREP76_PPC tab If the combination of "P26865-B21" and "SI" should match as per "PPC_Val" tab Column A and Column C. Also, we need to ensure that in "ZVREP76_PPC" tab that the combination of "P26865-B21" and "SI" is repeated for 71 times.
If the above condition is met then the result should appear as "Passed" in Cell K2 in "PPC_Val" tab or else it should be failed.
Example
- In PPC_Val tab CELL C2, if the value is XX then the result should be failed since in ZVREP76_PPC Tab the value exists as SI for the Material P26865-B21.
- In PPC_Val tab CELL C2, if the value is SI and in ZVREP76_PPC Tab the value exists as XX under column G for the Material P26865-B21 then it should be Failed.
- In PPC_Val tab CELL C2, if the value is SI and in ZVREP76_PPC Tab the value exists as SI under column G for the first 3 lines for rest the value is "XX" for the Material P26865-B21 then it should be Failed.
- In PPC_Val tab CELL C2, if the value is SI for the Material P26865-B21 and in Tab ZVREP76_PPC Tab the combination of SI and the Material P26865-B21 is repeated for 70 times instead of 71 times then it should be Failed.
- It should be PASSED, if the combination of Material P26865-B21 and Div from SI from "PPC_Val" tab matches in ZVREP76_PPC Tab and repeated for 71 times.
I hope I have clearly explained the requirement and thank you for all your help in advance.
Regards,
Ranjith