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

Edward13

New Member
Joined
Aug 20, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.

Excel Formula:
=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 1YesYesNo
Person 2YesYesYes
Person 3NoYesNo
Person 4YesYesYes
 

Attachments

  • Screenshot 2023-08-20 153205.png
    Screenshot 2023-08-20 153205.png
    79.7 KB · Views: 8

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Would a (seemingly more straightforward) solution to this problem be to use the following formula? Are there any obvious issues with this approach?

Excel Formula:
=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).
 

Attachments

  • Screenshot 2023-08-20 182317.png
    Screenshot 2023-08-20 182317.png
    77.5 KB · Views: 4
Upvote 0
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
ABCDEFGHIJ
1NameAttribute 1Attribute 2Attribute 3Attribute 4Attribute 5Class 1Class 2Class 3
2Person 1AA.1AA.3YesYesNo
3Person 2AA.2AA.4AA.5YesYesYes
4Person 3AA.3AA.4NoYesNo
5Person 4AA.1AA.2AA.3AA.4AA.5YesYesYes
6
7
8
9Class 1AA.1AA.2
10Class 3AA.5
11Class 2AA.3AA.4
Sheet1
Cell Formulas
RangeFormula
H2:J5H2=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.
 
Upvote 0
Solution
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.
 
Upvote 0
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. (y)

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. :)
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top