Identify a given string

[leofeltrin]

Hi,

I have a list of options:

Análisis, Estimación, Planificación

Construcción

Pruebas de Conectividad

Revisión de Código y Corrección de Incidencias

Documentación de Transporte

And I need to identify which one is comming on each row of the worksheet.

Additionaly, as these strings may not always be the same, I'm searching for specific key words from each, by using the SEARCH formula, as follows, and once I identify it, I assign a "task type".

All these, resulted on the following formula, which is not working properly... it only works with the first FALSE option, but not with the rest.

=IF(A2="Requerimiento";C$2;IF(SEARCH("lisis";B2)<>"#VALUE!";C$3;IF(SEARCH("Construcc";B2)<>"#VALUE!";C$4;IF(SEARCH("Prueba";B2)<>"#VALUE!";C$5;IF(SEARCH("Revisi";B2)<>"#VALUE!";C$6;IF(SEARCH("Document";B2)<>"#VALUE!";C$7;))))))

Task Type	Tasks	Task Code	Formula
Requerimiento	Requerimiento	Req	Req
Tarea	Análisis, Estimación, Planificación	Task_Analisis	Task_Analisis
Tarea	Construcción	Task_Construc	#VALUE!
Tarea	Pruebas de Conectividad	Task_Prueba	#VALUE!
Tarea	Revisión de Código y Corrección de Incidencias	Task_Revision	#VALUE!
Tarea	Documentación de Transporte	Task_Transp	#VALUE!

Any suggestions?

Thanks

[#2 · Accepted solution]

@leofeltrin ,

Instead of

SEARCH("lisis";B2)<>"#VALUE!"

use

ISNUMBER(SEARCH("lisis";B2))

to check if the word is found or not. And be careful with absolute/relative references (perhaps it shall be $A$2 instead of A2 at the beginning, etc). I didn't check entire logic.

[#3]

@Sergei BaklanIt worked OK!! Thank you!

[#4]

@leofeltrin , glad to help