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
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets 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 days agoThanks heaps, this worked.
To post as a guest, your comment is unpublished.· 1 months ago감사합니다 정말 진심으로 감사드립니다
한달여간을 네이버에서만 찾다가 구글을 통해 찾으니 이렇게 바로 정확한 정보가 뜨네요 정말 감사드립니다 !
To post as a guest, your comment is unpublished.· 7 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.· 10 months agoHow would I make this work if there were blank cells in the D column?
To post as a guest, your comment is unpublished.· 1 years agoVery useful fomula.
Can we grab that value found from the search? (i.e. I want to check if the address field containing the 4 digit post code within the range, if yes then show value of the 4 digits post code.)
To post as a guest, your comment is unpublished.· 1 years agoHi, David,
My be this array formula can help you:
After pasting the formula, please press Shift + Ctrl + Enter keys together.
This formula is somewhat complex, please change the cell references to your need.
Please try it, hope it can help you!
To post as a guest, your comment is unpublished.· 6 days agoMyself and another gal have been trying to figure this one out for 2 days for a data problem we have to resolve. Thank you for this expression. It is exactly what we needed.
To post as a guest, your comment is unpublished.· 7 months agoi have a list of ingredients in column A, each cell has the whole ingredients of a product so its not just one word, in column D i have a the list of ingredients im trying to find within column A, how do i that? this formula doesnt seem to work 100% for this
To post as a guest, your comment is unpublished.· 7 months agodoes this formula work when there is a couple of words in the lookup cells and couple of words in the search in cells? basically i have lists of ingredients and want to locate some specific ingredient from within those list and the specific ingredient might be more than one word
To post as a guest, your comment is unpublished.· 10 months agoThis is amazing! One big issue for me however: What if there are blank cells in my "search for" D2:D4 list. For me it ruins the formula and I just get "NUM" in a few places. THANKS!
To post as a guest, your comment is unpublished.· 1 years agoAmazing, it works exactly the way that I needed. Thank you Champs.
To post as a guest, your comment is unpublished.· 1 years agoGlad to help you!
To post as a guest, your comment is unpublished.