0

In Sheet1, I have a bunch of rows with a text value in column A. In the same sheet in column B, I want to show "yes" or "no" based on if somewhere in that text value is a match from a list on a separate sheet, Sheet2. So the separate sheet would have a single column, and then "RandomText1" in row1, "RandomText2" in row2, "RandomText3" in row3, etc. In my original Sheet, Sheet 1, again - if any of those values from Sheet2 show up in the Value column of Sheet 1, then the Check column says "Yes", otherwise "No"

I just need the formula to do this automatically in Column B (Check)

Sheet 1
| Value              | Check          |
| --------           | -------------- |
| asdfasdfdeee       | No             |
| asRandomText2ddd   | Yes            |

Sheet 2
| Projects       | 
| --------       | 
| RandomText1    | 
| RandomText2    |
Chris Eaheart
  • 548
  • 2
  • 7
  • 25
  • 2
    `=IF(SUMPRODUCT(IFERROR(SEARCH(Sheet2!$A$2:$A$3,A2),0))>0,"Yes","No")` – tigeravatar Aug 26 '22 at 14:21
  • 3
    You can use `=SUM(COUNTIF(A2,"*"&Sheet2!A$2:A$3&"*"))>0`, or swap `SUM()` for `SUMPRODUCT()` if no auto-CSE with versions of Excel prior to ms365. Either way, the duplicates should get you there. – JvdV Aug 26 '22 at 14:30

0 Answers0