ISNUMBER(SEARCH) for multiple values, too many commands

[ttianna]

I want to use ISNUMBER and SEARCH to return specific values if certain values are found in another cell, but I have too many.

=IF(ISNUMBER(SEARCH("assessment",D4)),"Graded",IF(ISNUMBER(SEARCH("Aplia",D4)),"Graded",IF(ISNUMBER(SEARCH("Other",D4),"Not Graded"))))

I want it to return "Graded" if it finds "Assessment" or "Aplia", and "Not Graded" if it finds "Other", and a FALSE return if it doesn't find any of those terms.

What I get now with this formula is an error that says:
"You've entered too many arguments for this function."

I tried using an OR command to nest further, but that didn't work either.

 

[jtakw]

Hi,

You have "Too many arguments...." because you have a "value if false" before your second IF statement and subsequently, this will fix it:

Also, I don't recommend using a SPACE ( " " ) as a result if false, it will make future comparisons/math problematic.

Book1
	C	D	I	J
1				TRANSPORT
2				TRANSPORT
5
6	transport
Sheet137

Cell Formulas
Range	Formula
J1	=IF(ISNUMBER(SEARCH("PETROL",C6)),"TRANSPORT",IF(ISNUMBER(SEARCH("TRANSPORT",C6)),"TRANSPORT",IF(ISNUMBER(SEARCH("HARDWARE",C6)),"HARDWARE","")))
J2	=IF(OR(ISNUMBER(SEARCH({"PETROL","TRANSPORT"},C6))),"TRANSPORT",IF(ISNUMBER(SEARCH("HARDWARE",C6)),"HARDWARE",""))

J1 formula fixes your too many argument issue, using a Blank rather than a Space for "value if false".
Alternatively, use J2 formula, a bit shorter.

 

[Aladin Akyurek]

Hi, I need a favor for this formula where I don't need return value to be "FALSE". I used this:

=IF(ISNUMBER(SEARCH("PETROL",C6)),"TRANSPORT"," ",IF(ISNUMBER(SEARCH("TRANSPORT",C6)),"TRANSPORT"," ",IF(ISNUMBER(SEARCH("HARDWARE",C6)),"HARDWARE"," ")))

But it returns the error message "Too many arguments were entered for this function".Please help.

Does the following meet what you need?

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH({"hardware","petrol","transport"},C6),{"hardware","petrol","transport"}),"")

 

[needhelpbad23]

I have a similar issue. I want to return three possible options First, Second or Other. This is what I have so far =IF(ISNUMBER(SEARCH("*1st Lien*", '[Book3] Column Name'!C10), "Second Lien", IF(ISNUMBER(SEARCH("*2nd Lien*", '[Book3] Column Name'!C10), "Second Lien", IF(ISNUMBER(SEARCH("*S*", '[Book3] Column Name'!C10), "Other", '''')

 

[needhelpbad23]

I have a similar issue. I want to return three possible options First, Second or Other. This is what I have so far =IF(ISNUMBER(SEARCH("*1st Lien*", '[Book3] Column Name'!C10), "Second Lien", IF(ISNUMBER(SEARCH("*2nd Lien*", '[Book3] Column Name'!C10), "Second Lien", IF(ISNUMBER(SEARCH("*S*", '[Book3] Column Name'!C10), "Other", '''')

solved it. nvm