Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Categorizing based on multiple conditions & criteria containing text

  1. #1
    Seeker AnnaB's Avatar
    Join Date
    May 2018
    Posts
    7
    Articles
    0
    Excel Version
    2016

    Exclamation Categorizing based on multiple conditions & criteria containing text



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello - I am looking for help with the following. Thanks in advance!

    I have a column with values that need to be categorized based on multiple criteria. I would like to categorize using below logic:

    If value contains "X" Or "Y", categorize as "Category1", if contains "A" or "B" or "C" categorize as Category2, if contains "M" or "N" or "O", categorize as category 3, else "other"

    Can I use the IF OR statement for this?


    Thanks!

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,477
    Articles
    0
    Excel Version
    Excel 2016
    Assuming value in A2, try:

    =IF(OR(A2={"X","Y"}),"Category1",IF(OR(A2={"A","B","C"}),"Category2",IF(OR(A2={"M","N"}),"Category3","")))


  3. #3
    Seeker AnnaB's Avatar
    Join Date
    May 2018
    Posts
    7
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by NBVC View Post
    Assuming value in A2, try:

    =IF(OR(A2={"X","Y"}),"Category1",IF(OR(A2={"A","B","C"}),"Category2",IF(OR(A2={"M","N"}),"Category3","")))


    Thank you so much for the quick reply! That seems to be mostly what I'm looking for. How would you alter this to include contains? So instead of exactly matching "A" for example, I want it to say if it contains "A". Would that just be "*A*" instead?

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,477
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =IF(OR(ISNUMBER(SEARCH({"X","Y"},A2))),"Category1",IF(OR(ISNUMBER(SEARCH({"A","B","C"},A2))),"Category2",IF(OR(ISNUMBER(SEARCH({"M","N"},A2))),"Category3","")))


  5. #5
    Seeker AnnaB's Avatar
    Join Date
    May 2018
    Posts
    7
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by NBVC View Post
    Try:

    =IF(OR(ISNUMBER(SEARCH({"X","Y"},A2))),"Category1",IF(OR(ISNUMBER(SEARCH({"A","B","C"},A2))),"Category2",IF(OR(ISNUMBER(SEARCH({"M","N"},A2))),"Category3","")))
    Perfect, that works. Thank you!

  6. #6
    Seeker AnnaB's Avatar
    Join Date
    May 2018
    Posts
    7
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by AnnaB View Post
    Perfect, that works. Thank you!
    Actually, I do have one more follow up questions. How would you recommend I go about blanks vs. "other". I see you have an else "" in your statement. Right now, this would capture both blanks as well as categories that I am not specifying in the statement but I would like to be able to differentiate between "other" and blanks.
    Thanks,

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,477
    Articles
    0
    Excel Version
    Excel 2016
    Do you mean?

    =IF(A2="","",IF(OR(ISNUMBER(SEARCH({"X","Y"},A2))),"Category1",IF(OR(ISNUMBER(SEARCH({"A","B","C"},A2))),"Category2",IF(OR(ISNUMBER(SEARCH({"M","N"},A2))),"Category3","Other"))))


  8. #8
    Seeker AnnaB's Avatar
    Join Date
    May 2018
    Posts
    7
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by NBVC View Post
    Do you mean?

    =IF(A2="","",IF(OR(ISNUMBER(SEARCH({"X","Y"},A2))),"Category1",IF(OR(ISNUMBER(SEARCH({"A","B","C"},A2))),"Category2",IF(OR(ISNUMBER(SEARCH({"M","N"},A2))),"Category3","Other"))))

    Yes, I realized it was just a combination of the two different type of statements above as you also stated here. Thanks again, this was a huge help!

  9. #9
    Seeker AnnaB's Avatar
    Join Date
    May 2018
    Posts
    7
    Articles
    0
    Excel Version
    2016
    Hello,
    As a follow up, I wanted to try and use vlookup instead of the above formula for a larger dataset.
    I am currently using the below formula.

    =IFERROR(VLOOKUP(M2,lookuptable,2,0), "Other")

    While this does give me the correct matches, currently it returns anything that does not match my lookup table as "other" including blank cells. I would like to differentiate between blank cells (it should return as blank or “”) and non matched values which would include cells that have values but don’t match any of the lookup values (should be returned as “other”). If you could please help with how to update this formula, would really appreciate it.

    Thanks!

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,477
    Articles
    0
    Excel Version
    Excel 2016
    If it is returning "Other" for matches that have a blank in column 2 of the table, then it should be returning a 0 or blank, not "Other". The only way "Other" would come up is if M2 is blank or has a value that is not found in the first column of the table.... which is it?


Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •