If statement help needed
 

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("Departm ent",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?

If statement help needed
 

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.


"ah" wrote:

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("Departm ent",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?

If statement help needed
 

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("Departm ent",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

"ah" wrote:

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("Departm ent",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?

If statement help needed
 

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

"ah" wrote:

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.


"ah" wrote:

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("Departm ent",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?

If statement help needed
 

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("Departm ent",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

If statement help needed
 

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.
--
Regards

Roger Govier


"ah" wrote in message
...
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("Departm ent",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?

If statement help needed
 

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

"Toppers" wrote:

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

"ah" wrote:

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.


"ah" wrote:

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("Departm ent",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?

If statement help needed
 

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!

"Roger Govier" wrote:

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.
--
Regards

Roger Govier


"ah" wrote in message
...
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("Departm ent",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?

If statement help needed
 

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" wrote:

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

"ah" wrote:

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.


"ah" wrote:

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("Departm ent",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?

If statement help needed
 

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

"ah" wrote:

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" wrote:

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

"ah" wrote:

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.


"ah" wrote:

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("Departm ent",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?

If statement help needed
 

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.

--
Regards

Roger Govier


"Toppers" wrote in message
...
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!

"Roger Govier" wrote:

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.
--
Regards

Roger Govier


"ah" wrote in message
...
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("Departm ent",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?

If statement help needed
 

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)

--
Regards

Roger Govier


"Toppers" wrote in message
...
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!

"Roger Govier" wrote:

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.
--
Regards

Roger Govier


"ah" wrote in message
...
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("Departm ent",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?

If statement help needed
 

See Roger's last reply.

"ah" wrote:

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" wrote:

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

"ah" wrote:

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.


"ah" wrote:

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("Departm ent",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?

If statement help needed
 

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

"Roger Govier" wrote:

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)

--
Regards

Roger Govier


"Toppers" wrote in message
...
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!

"Roger Govier" wrote:

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.
--
Regards

Roger Govier


"ah" wrote in message
...
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("Departm ent",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?

If statement help needed
 

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

"Toppers" wrote:

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

"ah" wrote:

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" wrote:

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

"ah" wrote:

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.


"ah" wrote:

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("Departm ent",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?

If statement help needed
 

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

"Toppers" wrote:

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

"ah" wrote:

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" wrote:

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

"ah" wrote:

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.


"ah" wrote:

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("Departm ent",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?

If statement help needed
 

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.
--
Regards

Roger Govier


"ah" wrote in message
...
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

"Toppers" wrote:

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

"ah" wrote:

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" wrote:

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

"ah" wrote:

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.


"ah" wrote:

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("Departm ent",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?