1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Match whether a cell value contains a substring in an array

Discussion in 'Ask an Excel Question' started by Murari Rajagopalan, Apr 13, 2018.

  1. Murari Rajagopalan

    Murari Rajagopalan New Member

    Messages:
    29
    Hello there,
    I am trying to match a specific cell value in column B with the data in Column A and expecting the result in Column C as Yes or No.. In the sense, Column A data should be checked with all values in Column B and if there is a find, it should print as Yes. There will be only one value expected to be matched with Column B as well. Can someone help on this ?
    I have attached the excel file as well.

    Regards
    Murari Rajagopalan

    Attached Files:

  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,457
    Hi ,

    See if this is OK.

    Narayan

    Attached Files:

  3. Murari Rajagopalan

    Murari Rajagopalan New Member

    Messages:
    29
    Hello Narayan,
    I just tried by changing the value and this is not working. can you please help to check once ?

    Attached Files:

  4. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,594
    Maybe,

    C2, copied down :

    =IF(ISNUMBER(LOOKUP(1,-SEARCH($B$2:$B$10,A2))),"Yes","No")

    Regards
    Bosco
    Thomas Kuriakose likes this.
  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,457
    Hi ,

    Sorry , but my proposed formula was wrong. This is more like it ; you can go with Bosco's solution.

    =IF(ISNUMBER(LOOKUP(999, SEARCH(B2, $A$2:$A$5))), "Yes", "No")

    This assumes that no cell will have more than 999 characters.

    Narayan
  6. Murari Rajagopalan

    Murari Rajagopalan New Member

    Messages:
    29
    thank you very much. can i use an arrary in column B instead of one specific value ? I did tried with the formula as attached and this seems to be not working.. In the sense, every column in cell A, will be checked with all values in Cell B rather than a specific value

    Attached Files:

  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,457
    Hi ,

    See if this is OK.

    Narayan

    Attached Files:

  8. Murari Rajagopalan

    Murari Rajagopalan New Member

    Messages:
    29
    Many thanks Narayan. this works.. thanks again for the help. Much appreciated !:awesome:

Share This Page