If statement help needed

[ah]

Hi;

I get an error when I use the following statement:

=IF(ISNUMBER(SEARCH("Cost
Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B",IF(ISNUMBER(SEARCH("Bank
Information",E12)),"C",IF(ISNUMBER(SEARCH("Department",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12)),"E",IF(ISNUMBER(SEARCH("Reports
To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G","Not OK")))))))

The error message is as follows:
The formula you type contains an error

Can anyone help me with this?

 

[ah]

Hi;

Is there a limit of the if else criteria that I can put in for excel? I
found that it doesn't allow me to put in more than 7 criterias.

 

[Mike H]

I hope someone comes up with something better because this is messy but it
does solve the problem:-

In A1=IF(ISNUMBER(SEARCH("Cost
Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B",IF(ISNUMBER(SEARCH("Bank
Information",E12)),"C",IF(ISNUMBER(SEARCH("Department",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12)),"E",IF(ISNUMBER(SEARCH("Reports",E12)),"F",""))))))

In A2=IF(ISNUMBER(SEARCH("Sal",E12)),"G","")
In A3=IF(AND(A1="",A2=""),"Not OK",CONCATENATE(A1,A2))

Mike

 

[Toppers]

There is a limit of 7, although to can have work around.

Look at VLOOKUP as an alternative solution: create table in column A & B
starting row 2

A B
Cost Center A
OM B

etc

=VLOOKUP(E12,$A$2:$A$10,2,0)

For error condition:

=IF(ISNA(VLOOKUP(E12,$A$2:$A$10,2,0)),"Not OK",VLOOKUP(E12,$A$2:$A$10,2,0))

HTH

 

[Rick Rothstein \(MVP - VB\)]

I get an error when I use the following statement:

=IF(ISNUMBER(SEARCH("Cost
Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B",IF(ISNUMBER(SEARCH("Bank
Information",E12)),"C",IF(ISNUMBER(SEARCH("Department",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12)),"E",IF(ISNUMBER(SEARCH("Reports
To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G","Not OK")))))))

The error message is as follows:
The formula you type contains an error

I think you are missing a closing parenthesis on the end of your formula.

Rick

 

[Roger Govier]

Hi

Unless you are using XL2007, there is a limit of 7 for nested functions.
If you create a list of your 7 items you are looking for in say cells
A1:A7, then you could use

=IF(SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,D1)))),"G","Not OK")

Note I have used Find as this is case sensitive as opposed to Search.
Clearly your list can be more than 7 items, just extend the range A1:A7
to cover the number of items concerned.

 

[Toppers]

Ignore my reply .... as you are looking a sub-string: misread your question!

 

[Toppers]

Roger,
There is a list of corresponding values (A,B,C etc) with the
search arguments so building on your solution with table in columns A & B:

=IF(SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,$E$12)))*ROW($A$1:$A$7)),INDEX($B$1:$B$7,SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,$E$12)))*ROW($A$1:$A$7)),0),"Not
OK")

I am sure you can improve on this!

 

[ah]

Hi;

Thanks for your help.
But, I'm confused now. Let me tell you what I want so that you can assist me:

I want the system to assign a value based on the following pre defined table:
If Cell A1 contains the word called "Cost Center", then assign the value 0.5
to me
If Cell A1 contains the word called "Department", then assign the value 1.0
to me
If Cell A1 contains the word called "Bank", then assign the value 1.2 to me
and etc

FYI, I have about 30 criterias to fulfill.
What are the best approach that I can use?
Please advice.
Thanks in advance

 

[Toppers]

Does A1 only contain the text you want to test or is a sub-set e.g it could
contain "XYZ Cost Center"?

 

[Roger Govier]

Hi John

Is it not just a case of
=SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,D1)))*$B$1:$B$7)
or am I missing something.

 

[Roger Govier]

Sorry the reference cell you are using is E12 so that should be

=SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$4,$E$12)))*$B$1:$B$4)

 

[Toppers]

See Roger's last reply.

Hi;

Thanks for your help.
But, I'm confused now. Let me tell you what I want so that you can assist me:

I want the system to assign a value based on the following pre defined table:
If Cell A1 contains the word called "Cost Center", then assign the value 0.5
to me
If Cell A1 contains the word called "Department", then assign the value 1.0
to me
If Cell A1 contains the word called "Bank", then assign the value 1.2 to me
and etc

FYI, I have about 30 criterias to fulfill.
What are the best approach that I can use?
Please advice.
Thanks in advance

 

[Toppers]

It is now that the second values are numeric (according to OP's last posting)!

 

[ah]

Hi;
Thanks for your reply.

Yup, "cost center" is just part of the word.
For example: US cost center - international, UK cost center - local and etc

 

[ah]

Sorry, I'm totally lost now.
Can anyone guide me on how to go about this?

I want the system to assign a value based on the following pre defined table:
If Cell A1 contains the word called "Cost Center", then assign the value 0.5
to me

If Cell A1 contains the word called "Department", then assign the value 1.0
to me

If Cell A1 contains the word called "Bank", then assign the value 1.2 to me
and etc

FYI, I have about 30 criterias to fulfill.
What are the best approach that I can use?

thanks in advance

Hi;
Thanks for your reply.

Yup, "cost center" is just part of the word.
For example: US cost center - international, UK cost center - local and etc

 

[Roger Govier]

Hi

If that's the case, then maybe you would be better off with a Vlookup.
Create a table on another Sheet (Sheet2) with your 30 or so items in
A1:A30.
Alongside each set the values in B1:B30 that you want assigned to each
Mark the block of A1:B30 and in the small white pane above row number
and left of column A (the name box) type myData and press Enter.

On your Sheet1, in cell B1
=IF(A1="","",IF(ISERROR(VLOOKUP(A1,myTable,2,0)),"",VLOOKUP(A1,myTable,2,0)))
Copy down column B as far as required.
Now anything typed in A1, will return the value associated with that
item.