Categorizing based on multiple conditions & criteria containing text

AnnaB

New member
Joined
May 23, 2018
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2016
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!
 
Assuming value in A2, try:

=IF(OR(A2={"X","Y"}),"Category1",IF(OR(A2={"A","B","C"}),"Category2",IF(OR(A2={"M","N"}),"Category3","")))
 
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?
 
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","")))
 
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!
 
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,
 
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"))))
 
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!
 
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!
 
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?
 
Hi - I was able to figure out that it was returning "other" because I had not specified other vs. blank. I updated the formula to =IF(J2="","",IFERROR(VLOOKUP(J2,lookuptable,2,0), "Other")) and I was able to get what I needed. Thanks!
 
Back
Top