Cookies help us deliver our services. By using our services, you agree to our use of cookies.
Tip: Other languages are Google-Translated. You can visit the English version of this link.
or

Register

or

## 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.

Check if a cell contains one of several values with formulas

#### Check if a cell contains one of several values with formulas

###### Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!

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:

Notes:

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

#### Office Tab

Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

#### Kutools for Excel

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...

Say something here...
symbols left.
###### or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
• To post as a guest, your comment is unpublished.
· 2 days ago
Thanks 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 ago
hi
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.
· 7 months ago
first attachment didnt upload, see below attachment
• To post as a guest, your comment is unpublished.
· 10 months ago
How 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 ago
Very 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.)
David
• To post as a guest, your comment is unpublished.
· 1 years ago
Hi, David,
My be this array formula can help you:
=IF(SUMPRODUCT(IF(ISNUMBER(SEARCH(\$D\$2:\$D\$4,A2)),ROW(\$D\$2:\$D\$4)-ROW(\$D\$2)+1,0))>0,INDEX(\$D\$2:\$D\$4,SUMPRODUCT(IF(ISNUMBER(SEARCH(\$D\$2:\$D\$4,A2)),ROW(\$D\$2:\$D\$4)-ROW(\$D\$2)+1,0))))
After pasting the formula, please press Shift + Ctrl + Enter keys together.
This formula is somewhat complex, please change the cell references to your need.
• To post as a guest, your comment is unpublished.
· 6 days ago
Myself 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 ago
i 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
thanks
• To post as a guest, your comment is unpublished.
· 7 months ago
does 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 ago
This 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 ago
Amazing, it works exactly the way that I needed. Thank you Champs.
• To post as a guest, your comment is unpublished.
· 1 years ago
• To post as a guest, your comment is unpublished.
· 1 years ago
hi, would you please explain how you got that function above? I'm trying to do the same thing but cannot seem to change the cell references correctly.
• To post as a guest, your comment is unpublished.
· 11 months ago
Yes please, I need this too. I can't see why \$D\$2 is locked?