Nested IF Formula with "contains"

[Roar]

Hi, thank you for reading.

What I am looking for is a formula like

If B18 Contains Apple, then I18 = "$6000" if B18 contains Essential, then I18=$12000" if B18 contains Access then I18 = "22000"

Can something like that work? I just need it to find one word in a large paragraph of text.

Oh, and I guess one more variable is that B18 is actually a merged cell covering B18:g18 so I hope it is okay to use B18?

Thank you for your help, I have tried about 10 things from the internet.

 

[Scott Huish]

re: Nested IF Formula with "contains"

=LOOKUP(9.999999999E+307,(SEARCH({" Apple "," Essential "," Access "}," " &B18&" ")),{6000,12000,22000})

Format result cell as you like.

This does assume there is only one occurrence of one of the words.

 

[Jonmo1]

Try

=SUM(COUNTIF(B18,"*"&{"Apple","Essential","Access"}&"*")*{6000,12000,22000})

 

[Scott Huish]

I like that. The only problem I see is that it wouldn't check for exact words.

For example, Essentially would also match in that formula.