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

Office Tab

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

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

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

Screen shot of Kutools for Excel

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.
    Reece · 2 days ago
    Thanks heaps, this worked.
  • To post as a guest, your comment is unpublished.
    goldwater · 1 months ago
    감사합니다 정말 진심으로 감사드립니다
    한달여간을 네이버에서만 찾다가 구글을 통해 찾으니 이렇게 바로 정확한 정보가 뜨네요 정말 감사드립니다 !
  • To post as a guest, your comment is unpublished.
    sam · 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.
      sam · 7 months ago
      first attachment didnt upload, see below attachment
  • To post as a guest, your comment is unpublished.
    Alex Ezorsky · 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.
    Thach David Ho · 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.
      skyyang · 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.
      Please try it, hope it can help you!
      • To post as a guest, your comment is unpublished.
        Tina M Morris · 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.
        sam · 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.
        sam · 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.
        Alex Ezorsky · 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.
        Thach David Ho · 1 years ago
        Amazing, it works exactly the way that I needed. Thank you Champs.
        • To post as a guest, your comment is unpublished.
          skyyang · 1 years ago
          Glad to help you!
          • To post as a guest, your comment is unpublished.
            geenie · 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.
              Jon · 11 months ago
              Yes please, I need this too. I can't see why $D$2 is locked?