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.
Ideally, for this example, the results in Cells H2:J5 would be:
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 1 | Yes | Yes | No |
Person 2 | Yes | Yes | Yes |
Person 3 | No | Yes | No |
Person 4 | Yes | Yes | Yes |