Comparing if any value from one list is the same as any value in another list

[Edward13]

I am trying to Display "Yes" or "No" outcomes for whether any value in List 1 matches any value in List 2.

In the screenshot, I have tried to create a formula which for Cell H2 checks whether values B2:F2 match any of the values in B9:F9. If there is any matching value, I would like the result to be "Yes". If there is no matching value, I would like the value to be "No". For person 1, I would then like Cell I2 to compare values B2:F2 to B10:F10, and then Cell J2 to compare values B2:F2 to B11:F11.

However, as you can see in the screenshot, my formula returns spill values for each check, rather than a single value.

=IF(ISNA(INDEX(Table6[@[Attribute 1 ]:[Attribute 5]], MATCH(B9:F9, Table6[@[Attribute 1 ]:[Attribute 5]], 0))),"No","Yes")

Ideally, for this example, the results in Cells H2:J5 would be:

	Class 1?	Class 2?	Class 3?
Person 1	Yes	Yes	No
Person 2	Yes	Yes	Yes
Person 3	No	Yes	No
Person 4	Yes	Yes	Yes

 

[Edward13]

Would a (seemingly more straightforward) solution to this problem be to use the following formula? Are there any obvious issues with this approach?

=IF(SUMPRODUCT(COUNTIF(Table6[@[Attribute 1 ]:[Attribute 5]],B9:F9))>0,"Yes","No")

Adopting the above outputs results which are consistent with the table in my question (see attachment).

 

[Peter_SSs]

Welcome to the MrExcel board!

Offering another approach. I have deliberately altered the order in rows 9:11 as this structure does not rely on the headings in H1:J1 matching the exact order in A9:A11

Edward13.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Name	Attribute 1	Attribute 2	Attribute 3	Attribute 4	Attribute 5		Class 1	Class 2	Class 3
2	Person 1	AA.1	AA.3					Yes	Yes	No
3	Person 2	AA.2	AA.4	AA.5				Yes	Yes	Yes
4	Person 3	AA.3	AA.4					No	Yes	No
5	Person 4	AA.1	AA.2	AA.3	AA.4	AA.5		Yes	Yes	Yes
6
7
8
9	Class 1	AA.1	AA.2
10	Class 3	AA.5
11	Class 2	AA.3	AA.4
Sheet1

Cell Formulas
Range	Formula
H2:J5	H2	=IF(COUNT(MATCH(Table6[@[Attribute 1]:[Attribute 5]],FILTER($B$9:$F$11,$A$9:$A$11=H$1,""),0)),"Yes","No")

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

 

[Edward13]

Thanks, Peter. That is an elegant solution, and much more easily scalable than my cobbled together formula.

Apologies for not providing the data in XL2BB - I'm using a work computer, and it doesn't allow me to download it.

 

[Peter_SSs]

Thanks, Peter. That is an elegant solution, and much more easily scalable than my cobbled together formula.

You're welcome. Thanks for the follow-up.

Apologies for not providing the data in XL2BB - I'm using a work computer, and it doesn't allow me to download it.

Fair enough. If you have questions in the future I suggest that you mention the workplace restriction in your first post so that we don't keep asking, and also remember that if the sample data is fairly small, you can just copy paste from Excel as you did partially in post #1 here.