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.

[TABLE="class: grid, width: 76"]
<tbody>[TR]
[TD="width: 76"]CEO[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Marketing

Text: [TABLE="width: 179"]
<tbody>[TR="class: grid"]
[TD="width: 179"]Marketing Sales

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
First look for "CEO", you can not find it. Then look for "Sales", find it and put Sales.

---

[TABLE="class: grid, width: 76"]
<colgroup><col width="76" style="width:57pt"> </colgroup><tbody>[TR]
[TD="width: 76"]Avatar[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Marketing[/TD]
[/TR]
[TR]
[TD]Founder[/TD]
[/TR]
[TR]
[TD]President[/TD]
[/TR]
</tbody>[/TABLE]


Text:

[TABLE="class: grid, width: 400"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Vice President of Sales

Look for sales, find sales.

[/TD]
[/TR]
[TR]
[TD]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.[/TD]
[/TR]
</tbody>[/TABLE]

 

[sunrise06]

Thanks for this explanation. It is very helpful.

 

[DanteAmor]

You're welcome.