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!

 

[Michael M]

What formula are you using ???
I'm guessing a simple VLOOKUP would be all you need ??

 

[DanteAmor]

Tell me the desired result for "Marketing Sales", there are 3 possibilities:
- Marketing
- Sales
or
- Marketing and Sales

 

[sunrise06]

Michael, the attached document shows you the formula. It is not a simple VLOOKUP as it is a "contains" argument, per se.

 

[sunrise06]

Dante Armor, I am assuming Marketing Sales would return Marketing and Sales Marketing would return Sales, not Sales for both. Then again, I do not have a clear understanding of their formula.

 

[Michael M]

As I can't open the attached....I asked the question ????

 

[DanteAmor]

What formula are you using ???
I'm guessing a simple VLOOKUP would be all you need ??

I share the formula.


=IF(ISNUMBER(search(Validation!$S$2,$F2)),Validation!$S$2,IF(ISNUMBER(search(Validation!$S$3,$F2)),Validation!$S$3,
IF(ISNUMBER(search(Validation!$S$4,$F2)),Validation!$S$4,
IF(ISNUMBER(search(Validation!$S$5,$F2)),Validation!$S$5,
IF(ISNUMBER(search(Validation!$S$6,$F2)),Validation!$S$6,
IF(ISNUMBER(search(Validation!$S$7,$F2)),Validation!$S$7,
IF(ISNUMBER(search(Validation!$S$8,$F2)),Validation!$S$8,
IF(ISNUMBER(search(Validation!$S$9,$F2)),Validation!$S$9,"Other"))))))))

 

[DanteAmor]

Dante Armor, I am assuming Marketing Sales would return Marketing and Sales Marketing would return Sales, not Sales for both. Then again, I do not have a clear understanding of their formula.

Do not worry about the formula, the important thing is to say what result you want.

 

[DanteAmor]

The inconsistency is not of the formula. The inconsistency is in the data you have:


- Marketing Sales
- Sales Marketing
- Vice President of Sales
- Co-Founder and President



The texts belong to 2 avatar.

Avatar options:
[TABLE="class: grid, width: 76"]
<tbody>[TR]
[TD]CEO[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Marketing[/TD]
[/TR]
[TR]
[TD]Founder[/TD]
[/TR]
[TR]
[TD]President[/TD]
[/TR]
[TR]
[TD]Owner[/TD]
[/TR]
[TR]
[TD]Partner[/TD]
[/TR]
[TR]
[TD]Human Resources[/TD]
[/TR]
</tbody>[/TABLE]


You should explain a search pattern.

 

[sunrise06]

Thank you, as it is not my file I am not sure which one is preffered. I guess one of my questions is why do they return the same value and why do the Vice President of Sales and Vice President of Operations not return President for both?