Excel Nested IF INDEX MATCH Function

Question

Based on the rows in which column B:B equals a certain number, I need to determine whether or not specific strings exist in either of two columns. If one of the two columns is detected, I want it to return "rejected."

At first, I had only one column to search, so I used a straightforward Search Index Match Formular embedded within an if Statement. This operates without a hitch.

=if(SEARCH({"neg", "negativ", "negative", "negatives", "Bonität"},index(Input_Jira!$B$1:$N$2000,MATCH("*"&G90&"*",Input_Jira!B:B,0),2)),"rejected","")

To include the second column I added the same if search index match logic for the case the first if statement is false:

=if(SEARCH({"neg", "negativ", "negative", "negatives", "Bonität"}, index(Input_Jira!$B$1:$N$2000,MATCH("*"&G90&"*",Input_Jira!B:B,0),13)),"rejected",if(SEARCH({"neg", "negativ", "negative", "negatives", "Bonität"},index(Input_Jira!$B$1:$N$2000,MATCH("*"&G90&"*",Input_Jira!B:B,0),2)),"rejected",""))  

This function gives me a value error although the separate part of the functions works perfectly fine.

Answer 1

Use COUNTIF and an array if all you need to do is count the number of times a set of strings appears in a range:

E2 = =SUM(COUNTIF($A$1:$B$12;{"a","b"}))

E3 = =SUM(COUNTIF($A$1:$B$12;{"e";"f"}))

E4 = =SUM(COUNTIF($A$1:$B$12;{"a";"b"}))

If the result is zero, there is absolutely no match (Second case). This can be used in conjunction with an IF statement to accomplish any task. Moreover, COUNTIF can be applied to wildcars if you just need partial matches.