Combining two arguments (formulas) with non-consecutive cells using if/or

[Trip101]

I need help with this formula. I’m sure there’s a more efficient way to write the formula. Source cell where my formula resides is F4. Trying to insert ‘PM’ into F4 if cells S4, V4, Y4 and/or AB4 have numerical values of 24.2 or 12.2 or insert ‘SCAN’ into F4 if cells S4, V4, Y4 and/or AB4 have numerical values of 4 or 6. The formula below works with 12.2, 4 and 6 but when I enter 24.2 it populates F4 with ‘SCANPM’. Please help!

=IF(OR(ISNUMBER(SEARCH("24.2",S4)),ISNUMBER(SEARCH("24.2",V4)),ISNUMBER(SEARCH("24.2",Y4)),ISNUMBER(SEARCH("24.2",AB4)),ISNUMBER(SEARCH("12.2",S4)),ISNUMBER(SEARCH("12.2",V4)),ISNUMBER(SEARCH("12.2",Y4)),ISNUMBER(SEARCH("12.2",AB4))),"PM"," ") &IF(OR(ISNUMBER(SEARCH("4",S4)),ISNUMBER(SEARCH("4",V4)),ISNUMBER(SEARCH("4",Y4)),ISNUMBER(SEARCH("4",AB4)),ISNUMBER(SEARCH("6",S4)),ISNUMBER(SEARCH("6",V4)),ISNUMBER(SEARCH("6",Y4)),ISNUMBER(SEARCH("6",AB4))),"SCAN"," ")

 

[offthelip]

You can abbreviate your equations by concatenating the cells you are searching ie. change this:

OR(ISNUMBER(SEARCH("24.2",S4)),ISNUMBER(SEARCH("24.2",V4)),ISNUMBER(SEARCH("24.2",Y4)),ISNUMBER(SEARCH("24.2",AB4)

to

=(ISNUMBER(SEARCH("24.2",S4&V4&Y4&AB4)))

apply everywhere else in the same way

 

[Trip101]

You can abbreviate your equations by concatenating the cells you are searching ie. change this:

OR(ISNUMBER(SEARCH("24.2",S4)),ISNUMBER(SEARCH("24.2",V4)),ISNUMBER(SEARCH("24.2",Y4)),ISNUMBER(SEARCH("24.2",AB4)

to

=(ISNUMBER(SEARCH("24.2",S4&V4&Y4&AB4)))

apply everywhere else in the same way

Okay, I inserted =IF(ISNUMBER(SEARCH("24.2",S4&V4&Y4&AB4)),"PM"," ") and it works great but now I need to add a second string (formula) so that it will return the word 'SCAN' if a '4' or '6' is inserted. Something like this =IF(ISNUMBER(SEARCH("24.2",S4&V4&Y4&AB4)),"PM"," ") &IF(ISNUMBER(SEARCH("4.0",S4&V4&Y4&AB4)),"SCAN"," "). Basically, what I need is if '24.2' is entered into S4, V4, Y4 or AB4, it returns 'PM' in my source cell and/or if '4.0' is entered into S4, V4, Y4 or AB4, it returns 'SCAN' in my source cell. abbreviated formula works but returns 'PMSCAN'...

 

[Trip101]

I tried this and it returned 'PMTRUE' =IF(ISNUMBER(SEARCH("24.2",S4&V4&Y4&AB4)),"PM"," ") &OR(ISNUMBER(SEARCH("4",S4&V4&Y4&AB4)),"SCAN"," ")

 

[offthelip]

this should do all the conditions:

=IF(OR(ISNUMBER(SEARCH("24.2",S4&V4&Y4&AB4)),ISNUMBER(SEARCH("12.2",S4&V4&Y4&AB4))),"PM","") & IF(OR(AND(ISNUMBER(SEARCH("4",S4&V4&Y4&AB4)),NOT(ISNUMBER(SEARCH("24.2",S4&V4&Y4&AB4)))),ISNUMBER(SEARCH("6",S4&V4&Y4&AB4))),"SCAN","")

the problem is 4 is in 24.2 so it goes to SCAN. thus I had to put in the extra AND (....NOT)))

 

[Trip101]

Perfect! Thank you so much….

 

[jtakw]

Hi,

Also try this simplified version:

=IF(OR(ISNUMBER(SEARCH({"24.2","12.2"},S4&V4&Y4&AB4))),"PM","")&IF(OR(ISNUMBER(SEARCH({" 4 ","6"}," "&S4&" "&V4&" "&Y4&" "&AB4&" "))),"SCAN","")