CODEWITHSUNDEEP
excelexcel-formulams-office
hamishm
hamishm

Reputation: 11

Issue in using ISNUMBER SEARCH to find text values over a range of multiple cells in Excel

I have created a formula that searches a single cell for any words from a predefined list and if the word is present, a "Yes" is output. This is working fine however I now need it to search a range of cells for these values and this is resulting in a negative result.

The formula itself is:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Drug effects","Drug therapy","antagonist"},C2)))>0,"YES","NO")

This will successfully search cell C2 for those values and will output "Yes" if found. Similarly, if I update it to search D2 and it also contains those values, a "Yes" will also appear - all of this is expected and correct.

The issue I'm having is that I want it to search both C2 and D2 and give a "Yes" if a search term is found in either 1 or both of those cells. The formula I'm using is:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Drug effects","Drug therapy","antagonist"},C2:D2)))>0,"YES","NO")

This is resulting in a "No" and it shouldn't since the value is present in both C2 and D2. Can anyone offer a suggestion to get this formula working over a range of cells?

Upvotes: 0

Views: 665

Answers (1)

xidgel
xidgel

Reputation: 3145

I think the issue is that {"Drug effects","Drug therapy","antagonist"} and C2:D2 are both horizontal vectors and Excel does not know how to calculate that as an array formula. I can get your formula to work by making the first array vertical by either

(a) using semicolons instead of commas to separate the array elements:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Drug effects";"Drug therapy";"antagonist"},C2:D2)))>0,"YES","NO")

or

(b) by using TRANSPOSE:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(TRANSPOSE({"Drug effects","Drug therapy","antagonist"}),C2:D2)))>0,"YES","NO")

This will work as long as your search range C2:D2 is 1-dimensional and horizontal. For 1-dimensional and vertical you can apply TRANSPOSE to the search range, e.g.:

 =IF(SUMPRODUCT(--ISNUMBER(SEARCH({"Drug effects";"Drug therapy";"antagonist"},TRANSPOSE(C2:C3))))>0,"YES","NO")

Upvotes: 1

Related Questions