Need Help On Index Match Formula

[ranjith2523]

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.

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

1. 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.
2. 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.
3. 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.
4. 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.
5. 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

 

[Eric W]

How about:

=IF(COUNTIFS(ZVREP76_PPC!$B:$B,A2,ZVREP76_PPC!$G:$G,C2)=71,"Passed","Failed")

 

[ranjith2523]

How about:

=IF(COUNTIFS(ZVREP76_PPC!$B:$B,A2,ZVREP76_PPC!$G:$G,C2)=71,"Passed","Failed")

Simple but powerful.. I thought this needs to be done by Index Match Formula. Now you taught me my thought is incorrect.

This is what i expected. Thank you so much for your quick help here.

 

[Eric W]

Glad it works for you! Thanks for the feedback.