CODEWITHSUNDEEP
excelif-statement
Nadz
Nadz

Reputation: 103

ISNUMBER statement only reading first condition

Good day,

I am trying to insert this formula into excel to search for specific terms in a cell, and return a value "50" if the term is found.

I have been able to successfully implement this using the below primitive formula:

   =IF(C1394<>70,C1394,IF(ISNUMBER(SEARCH("*PD*",B1394)),"50",IF(ISNUMBER(SEARCH("*OD*",B1394)),"50",IF(ISNUMBER(SEARCH("*OC*",B1394)),"50",IF(ISNUMBER(SEARCH("*OF*",B1394)),"50",IF(ISNUMBER(SEARCH("*PC*",B1394)),"50",IF(ISNUMBER(SEARCH("*MS*",B1394)),"50",C1394)))))))

I tried to make my formula more efficient and dynamic using the below approach, however excel only reads the first condition "PD" and ignores the rest

=IF(C8266 =70,IF(--ISNUMBER(SEARCH({"*PD*","*OD*","*OC*","*OF*","*PC*","*MS*"},B8266)),"50",C8266),C8266)

Can someone please advise what am I doing wrong?

Upvotes: 0

Views: 100

Answers (2)

brucek
brucek

Reputation: 726

You're just missing an OR clause, see this: http://www.mrexcel.com/forum/excel-questions/601195-check-multiple-text-strings-cell.html#post2977162

So you want:

=IF(C8266=70,IF(OR(ISNUMBER(SEARCH({"*PD*","*OD*","*OC*","*OF*","*PC*","*MS*"},B8266))),"50",C8266),C8266)

Upvotes: 2

Jerry
Jerry

Reputation: 71548

What you are doing wrong is you are trying to evaluate an array within a formula that doesn't usually evaluate arrays. You evaluate such formulae by pressing and holding Ctrl+Shift and then press Enter.

Otherwise, your formula can be shortened to the below:

=IF(OR(ISNUMBER((C8266=70)*SEARCH({"PD","OD","OC","OF","PC","MS"},B8266))),"50",C8266)
  • SEARCH does not require wildcards.
  • A boolean multiplied by a number gives a number. An error multiplied by a number gives an error. So you can safely combine the first and second checks together and drop the --.
  • OR is then used to check if there is at least 1 number within the array of numbers and/or errors.

Similarly array invoked (with Ctrl+Shift+Enter).

Upvotes: 1

Related Questions