Please help me to simplify formula

[manish.agrawal446@gmail.com]

IF(ISNUMBER(SEARCH("APOLLO 8",N6)),"Apollo",

IF(ISNUMBER(SEARCH("BFUB 1.6.4",N6)),"Apollo",

IF(ISNUMBER(SEARCH("Fix Team",AB6)),"Defect fix team",

IF(ISNUMBER(SEARCH("IMPL & SUPP",C6)),"Implementation Support",

IF(ISNUMBER(SEARCH("BFUB15-MCP-Interface-Testing-Actinver",C6)),"Implementation Support",

IF(ISNUMBER(SEARCH("NBK-Performance",C6)),"NBK Performance",

IF(ISNUMBER(SEARCH("NBK - Performance",C6)),"NBK Performance",

IF(ISNUMBER(SEARCH("00",C6)),

IF(ISNUMBER(SEARCH("copk",C6)),"SFDC COPK",

IF(ISNUMBER(SEARCH("bcb",C6)),"SFDC BCB",

IF(ISNUMBER(SEARCH("boc",C6)),"SFDC BOC",

IF(ISNUMBER(SEARCH("SBA",C6)),"SFDC SBA",

IF(ISNUMBER(SEARCH("NBOK",C6)),"SFDC NBK",

IF(ISNUMBER(SEARCH("SBSA",C6)),"SFDC SBA",

IF(ISNUMBER(SEARCH("ACTINVER",C6)),"SFDC Actinver",

IF(ISNUMBER(SEARCH("ACT-",C6)),"SFDC Actinver",

IF(ISNUMBER(SEARCH("ACT_",C6)),"SFDC Actinver",

IF(ISNUMBER(SEARCH("crdb",C6)),"SFDC CRDB",

IF(ISNUMBER(SEARCH("NBK",C6)),"SFDC NBK",

"No Client SFDC Issue"

)

)

)

)

)

)

)

)

)

)

)

)

)

)

)))),"")


This value returns false if no value is found

Or can we simplify this formula

 

[Hui]

Manish


You could do something like

=INDEX(O11:O23,SUMPRODUCT(COUNTIF(N6,"*"&$N$11:$N$23&"*")*(ROW(N11:N23)-Row(N10))))

This is where N11:N23 have a list of the Lookup values

O11:O23 have a list of the associated values to be returned

eg: N11 would have Apollo 8 and O11 would have Apollo etc

Adjust ranges to suit


This extends on the technique discussed at: http://chandoo.org/wp/2012/02/09/formula-forensics-011/

 

[manish.agrawal446@gmail.com]

INDEX(CV2:CV21,SUMPRODUCT(COUNTIF(CU2,"*"&$CU$2:$CU$20&"*")*(ROW(CU2:CU20)-ROW(CU19))))


It is throwing error

 

[manish.agrawal446@gmail.com]

https://rapidshare.com/files/2722280117/Imp Support Report.xlsm


The formula is in defect workseet

copied in A2

 

[Luke M]

That last bit of formula is the problem, should be:

=INDEX(CV2:CV21,SUMPRODUCT(COUNTIF(CU2,"*"&$CU$2:$CU$20&"*")*(ROW(CU2:CU20)-ROW(CU1))))

 

[manish.agrawal446@gmail.com]

The o/p is not matching with the data which I was getting from 1st formula which I have mentioned in the post

 

[Hui]

Agrawal


A2: should be

=INDEX(CV$2:CV$21,SUMPRODUCT(COUNTIF(CU2,"*"&$CU$2:$CU$20&"*")* (ROW($CU$2:$CU$20)-ROW($CU$1))))

Copy down

Just needed a few $ signs to limit the movement of the look up ranges etc

 

[Montrey]

Hui in general terms, what does your formula do.

 

[Haseeb A]

Hello Agrawal,


If the below are your requirements:


A cell contains text eg: C6 & you have some text to SEARCH in C6 (consider texts are in CU2:CU21). If any of the text from CU2:CU21 CONTAINS in C6, return the appropriate text from CV2:CV21.


If so, try this formula,


=LOOKUP(9E+300,SEARCH(CU2:CU21,C6)/(CU2:CU21<>""),CV2:CV21)


If multiple texts found in C6, will return the last text's appropriate value.