Excel formula: "IF" a cell contains a word, then?

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($F$2:$F$12,B2)))>0,A2)

With $F$2:$F$12 => List of words you are looking for (each in a cell)

With B2 > the cell where you are searching

With A2 > what you return if true

If cell B2 contains one of the words from cells $F$2:$F$12, then it displays A2

Broken down:

* SEARCH(lookup text, text in which you search) returns the index of the character from which the searched word is found

* ISNUMBER returns TRUE if the result of SEARCH is a number (thus if one of the words is found)

* The problem with SEARCH is that it displays the result for each word in different cells: meaning that if you search for 5 words it will "expand" over 5 cells below your formula

>> The SUMPRODUCT function serves to concatenate these results to have them in 1 cell

* The IF function allows you to have a value for TRUE and/or FALSE instead of the words TRUE / FALSE

Source: https://fr.extendoffice.com/documents/excel/3796-excel-check-if-cell-contains-one-of-several-values.html#a1

Hello,

Can you help me with this formula please?

Here is the problem:
I have text in column A (name FIRSTNAME)
I would like to put a formula in column B that gives me a result based on part of the text from A.
Example: if A1 contains "Fabien" or "Franck" or other names (about 10 possibilities), then it returns "adv", otherwise nothing; or if A1 contains "Aline" or "Véro" or other names (about 10 possibilities), then it returns "tech", otherwise nothing.
Depending on the names, I would then have adv or tech in column B

Thank you in advance

Hello,

You can write the formula like this: the first formula for handling Male First Names

=IF(SUM(COUNTIF(A1,"Fabien"),COUNTIF(A1,"Frank"),COUNTIF(A1,"Pierre"),COUNTIF(A1,"Marc"))>0,"adv","")

the formula for Female First Names

IF(SUM(COUNTIF(A1,"Aline"),COUNTIF(A1,"Véro"),COUNTIF(A1,"Marie"),COUNTIF(A1,"Ginette"))=1,"tech","")

and you combine the two formulas with & between the two formulas

=IF(SUM(COUNTIF(A1,"Fabien"),COUNTIF(A1,"Frank"),COUNTIF(A1,"Pierre"),COUNTIF(A1,"Marc"))>0,"adv","")&IF(SUM(COUNTIF(A1,"Aline"),COUNTIF(A1,"Véro"),COUNTIF(A1,"Marie"),COUNTIF(A1,"Ginette"))=1,"tech","")

See you+
Mike-31

I am responsible for what I say, not for what you understand…