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.

Apr 1, 2023 in Others 832 views

## 1 answer to this question.

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.

• 63,720 points

## Excel worksheet multi-criteria function (like Index-Match array) to fetch last value of an item, looking up by item name and date

Use: =SUMIFS(C:C,B:B,E12,A:A,MAXIFS(A:A,B:B,E12)) It will return the value at the ...READ MORE

## IF function in combination with an Round function Excel

I'm not sure if it is a ...READ MORE

## Excel Formula multiple Index Match and Average the result

If the conditions are separate and unrelated, ...READ MORE

## Excel Formula - if values in columns all contain X then return

You can use the AND function: = IF(AND(A3="OK";B3="OK";C3="OK");"everything ...READ MORE

## (Excel) If cell is greater than <condition> then minus <number>

The IF function to calculate B5 (amount ...READ MORE

## IfError with else, does this function exist in Excel?

Next to IFERROR(), there also is the ISERROR() function, which ...READ MORE