Match with multiple conditions

[Tgandhi]

Hello all,

Thanks in advance for always helping out.

I am trying to create a formula that would show yes or match if the column A "Name" - ABCD has all the products "123, 321, 213 and 999" and result should show False or No or something in the event if a particular name does not contain all these 4 products.

Name
Column A Column B
ABCD 123
ABCD 321
ABCD 213
ABCD 999
XYZ 123
XYZ 321
XYZ 213

 

[JoeMo]

Book1
	A	B	C	D	E
1	ABCD	123		Name	All 4?
2	ABCD	321		ABCD	Yes
3	ABCD	213		XYZ	Yes
4	ABCD	999		RST	Yes
5	XYZ	123
6	XYZ	321
7	XYZ	213
8	RST	999
9	RST	321
10	RST	213
11	RST	123
12	XYZ	999
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=IF(AND(COUNTIFS($A$1:$A$12,$D2,$B$1:$B$12,123),COUNTIFS($A$1:$A$12,$D2,$B$1:$B$12,321),COUNTIFS($A$1:$A$12,$D2,$B$1:$B$12,213),COUNTIFS($A$1:$A$12,$D2,$B$1:$B$12,999)),"Yes","No")