Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to check if cell contains one of several values in Excel?

doc check if multiple values 1

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


arrow blue right bubble Check if a cell contains one of several values with formulas


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:

doc check if multiple values 2

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:

doc check if multiple values 3

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

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    shl · 2 months ago
    유용한 자료 감사합니다!
  • To post as a guest, your comment is unpublished.
    Reece · 3 months ago
    Thanks heaps, this worked.
  • To post as a guest, your comment is unpublished.
    goldwater · 4 months ago
    감사합니다 정말 진심으로 감사드립니다
    한달여간을 네이버에서만 찾다가 구글을 통해 찾으니 이렇게 바로 정확한 정보가 뜨네요 정말 감사드립니다 !
  • To post as a guest, your comment is unpublished.
    sam · 10 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.
      sam · 10 months ago
      first attachment didnt upload, see below attachment
  • To post as a guest, your comment is unpublished.
    Alex Ezorsky · 1 years ago
    How would I make this work if there were blank cells in the D column?