CODEWITHSUNDEEP
excelexcel-formula
SeT
SeT

Reputation: 224

Excel nested formula with multiple if(isnumber(search)) criteria with AND/OR conditions

I need to modify the below formula to search for multiple text criteria, in the specific cell and returns the respective value if matches. It does only work if I use it for 1 criteria:

=IF(AND(ISNUMBER(SEARCH("text1",A2)),OR(ISNUMBER(SEARCH("text2",A2))),OR(ISNUMBER(SEARCH("text3",A2)))),"Category 1","No")

I also need to include more criteria's in the same formula. I tried to use the below formula but it doesn't work correctly:

=IF(AND(ISNUMBER(SEARCH("text1",A2)),OR(ISNUMBER(SEARCH("text2",A2))),OR(ISNUMBER(SEARCH("text3",A2)))),"Category 1","No") & IF(AND(ISNUMBER(SEARCH("text1",A2)),OR(ISNUMBER(SEARCH("text2",A2))),OR(ISNUMBER(SEARCH("text4",A2)))),"Category 2","No") & IF(AND(ISNUMBER(SEARCH("text1",A2)),OR(ISNUMBER(SEARCH("text6",A2))),OR(ISNUMBER(SEARCH("text4",A2)))),"Category 3","No")

How to modify the above to display the correct category number? I will need to add around 20 different categories in my formula.

Example:

enter image description here

Formula:

=IF(AND(ISNUMBER(SEARCH("London",A2)),OR(ISNUMBER(SEARCH("Luton",A2))),OR(ISNUMBER(SEARCH("Cardiff",AH3298)))),"Category 1","No") & IF(AND(ISNUMBER(SEARCH("London",A2)),OR(ISNUMBER(SEARCH("Luton",A2))),OR(ISNUMBER(SEARCH("Dublin",AH3298)))),"Category 2","No") & IF(AND(ISNUMBER(SEARCH("London",A2)),OR(ISNUMBER(SEARCH("Manchester",A2))),OR(ISNUMBER(SEARCH("Paris",AH3298)))),"Category 3","No")

Thank you in advance!

Upvotes: 0

Views: 3674

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

If I have deduced your rules correctly, then the following might work for a straight formula approach (but see below):

=IF(AND(ISNUMBER(SEARCH({"London","Luton"},A2)),ISNUMBER(SEARCH("Paris",AH3298))),"Category 1",
IF(AND(ISNUMBER(SEARCH({"London","Luton"},A2)),ISNUMBER(SEARCH("Dublin",AH3298))),"Category 2",
IF(AND(ISNUMBER(SEARCH({"London","Manchester"},A2)),ISNUMBER(SEARCH("Cardif",AH3298))),"Category 3","No")))

However, for 20 categories, your formula would be about 2,000 characters long.

An approach using a Table, with clear-cut rules for your categorization, would be preferable as it should be much easier to maintain in the future.

Upvotes: 2

Related Questions