How to check if cell contains one of several values in Excel?
Supposing, you have a list of text strings in column A, now, you want to test each cell if it contains one of several values based on another range D2:D6. If it contains any of the specific text in D2:D6, it will display True, otherwise, it will show False as following screenshot shown. This article, I will talk about how to identify a cell if it contains one of several values in another range.
To check if a cell content contains any one of the text values in another range, the following formulas may help you, please do as this:
Enter this formula: =SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2)))>0 into a blank cell where you want to locate the result, B2, for instance, then drag the fill handle down to the cells that you want to apply this formula, and if the cell has any of the text values in another specific range, it will get True, otherwise, it will get False. See screenshot:
1. If you would like use “Yes” or “No’ to indicate the result, please apply this formula: =IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"Yes","No"), and you will get the following result as you need, see screenshot:
2. In the above formulas, D2:D6 is the specific data range which you want to check the cell based on, and A2 is the cell that you want to check.
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 2 months ago유용한 자료 감사합니다!
To post as a guest, your comment is unpublished.· 3 months agoThanks heaps, this worked.
To post as a guest, your comment is unpublished.· 4 months ago감사합니다 정말 진심으로 감사드립니다
한달여간을 네이버에서만 찾다가 구글을 통해 찾으니 이렇게 바로 정확한 정보가 뜨네요 정말 감사드립니다 !
To post as a guest, your comment is unpublished.· 10 months agohi
see attached file, im trying to find out if any of the ingredients from column D exists in column A and display whatever ingredient thats found in column B, is this possible?
To post as a guest, your comment is unpublished.· 1 years agoHow would I make this work if there were blank cells in the D column?