Excel IF statement doesn't working as intended

Question

Good day.

I'm trying to use the following if statement in an excel sheet to see if the contain any of the strings listed on 2 separated columns in a secondary sheet in the same excel file. The words are in Brazilian Portuguese but it doesn't really matter:

IF Statement: It searches the cell looking if it contains any word from the first list, else it searches the cell for any word from the second list, if it doesn't find anything them it just says inconclusive:

=IF(ISNUMBER(SEARCH('Sheet2'!$A$1:$A$14;D2));"Cancel";IF(ISNUMBER(SEARCH('Sheet2'!$B$1:$B$16;D2));"Sincronize";"Inconclusiv"))

List of words in Column A:

Cancelar
Cancelamento
Desativar
Desativação
Desativacao
Exclusão
Excluir

List of Words in Column B:

Sincronização
Sincronizar
Sincronia
Restaurar
Restauração
Restauracao
Recuperar
Recuperação

For whatever reason some times this works and some times it doesn't, so to test it out i decided to simply write "Cancelar" into every cell and use the statement above. It still doesn't work and i don`t know the reason, here is an example of what my excel sheet shows.

Cancelar; Cancel
Cancelar; Inconclusive
Cancelar; Inconclusive
Cancelar; Inconclusive
Cancelar; Inconclusive
Cancelar; Inconclusive
Cancelar; Inconclusive
Cancelar; Cancel

I don't know what is causing this error.

Answer 1

Wrap the ISNUMBER in SUMPRODUCT()

And, limit the SEARCH parameters to only the data on the list, if you include empty cells it will find a false positive. "" is found in everything.

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!$A$1:$A$7;D2)));"Cancel";IF(SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!$B$1:$B$8;D2)));"Sincronize";"Inconclusiv"))

Answer 2

I think you would be best using a VLOOKUP instead of the search.

=IF(VLOOKUP(D2;'Sheet2'!$A$1:$A$14;1;false)=D2;"Cancel";IF(VLOOKUP(D2;'Sheet2'!$B$1:$B$16;1;FALSE)=D2;"Sincronize";"Inconclusiv"))