# Thread: Categorizing based on multiple conditions & criteria containing text

1. ## 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. 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. Originally Posted by NBVC
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. 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. Originally Posted by NBVC
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. Originally Posted by AnnaB
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. 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. Originally Posted by NBVC
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. 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. 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 Last

#### Posting Permissions

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