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?
|