Lookup results seem unclear

[sunrise06]

I am trying to help a friend and I am confused by the results. In a separate column called "Avatar", they want to identify the role of each individual based on their title. For instance, if the title has the word SALES in it, they want the avatar to be SALES, if the title has the word MARKETING, they want the avatar to be MARKETING. The results they are getting are confusing. For example, look at my Sheets document and look as the results for Marketing Sales and Sales Marketing and Vice President of Sales and Vice President of Operations.

Can anyone explain why the “Avatar” being returned is inconsistent? I am not very familiar with this formula. Perhaps there is a better solution?

Here is my sample file: https://docs.google.com/spreadsheets/d/1QH7nb6ztmID63BhC8gCt8iEdcyu-RnnBkfPQ4uRvsN8/edit?usp=sharing

Thanks!

 

[sunrise06]

Thanks, Dante Amor, if it is found in two Avatar's how it is determined which one is returned? Does it return the first one? Is there a better formula to accomplish this?

 

[DanteAmor]

Thanks, Dante Amor, if it is found in two Avatar's how it is determined which one is returned? Does it return the first one? Is there a better formula to accomplish this?

how it is determined which one is returned?

This is determined by you or your friend.

The formula searches for each avatar within the text.
When it finds a match there it stops.

CEO

Sales

Marketing Text: Marketing Sales <tbody> </tbody>

<tbody>
</tbody>

First look for "CEO", you can not find it. Then look for "Sales", find it and put Sales.

---

Avatar

Sales

Marketing

Founder

President

<colgroup><col width="76" style="width:57pt"> </colgroup><tbody>
</tbody>

Text:

Vice President of Sales Look for sales, find sales.

Vice President of Operations Look for sales , do not find sales. Look for marketing, do not find marketing. look for founder, do not find founder look for president, find president.

<colgroup><col></colgroup><tbody>
</tbody>

 

[sunrise06]

Thanks for this explanation. It is very helpful.

 

[DanteAmor]

You're welcome.