Name charactors and table reference

[JoeExcelHelp]

Hi Everyone,

I have characters that exist within a string of names that correspond with a specific skill type
Im trying to find a way to match series of characters within each name to a table reference

I attached the excel that I hope is much clearer then my explanation

Thank You

[HansV]

In your example, Paul Smith has T4 and G, but not J. Why does he have Skill 1?

[JoeExcelHelp]

He has |J|

[HansV]

Oh sorry - you left the column too narrow, so that was not visible.

=IFS(ISNUMBER(SEARCH("|T4|", A2)*SEARCH("|G|", A2)*SEARCH("|J|",A2)),"Skill 1", ISNUMBER(SEARCH("|G|", A2)*SEARCH("|J|",A2)),"Skill 2", ISNUMBER(SEARCH("|G|", A2)),"Skill 3", ISNUMBER(SEARCH("|J|",A2)),"Skill 4", TRUE, "No Skills")

[JoeExcelHelp]

Any way of creating a more dynamic code/formula that references the table? If I change the table values the skill changes accord

[JoeExcelHelp]

Also unfort dont have the 'ifs' option

[snb]

="Skill " & CHOOSE(LEN(A2)-LEN(SUBSTITUTE(A2;"|";""));2;3+NOT(ISERROR(SEARCH("|J|";A2)));2;1)

[HansV]

It's an unfortunate setup. I'd change Table Reference to:

S2465.png

In B2 on Text Example, as an array formula, confirmed with Ctrl+Shift+Enter:

="Skill " &5-MIN(SUMPRODUCT(ISNUMBER(SEARCH("|"&'Table Reference'!$C$3:$C$5&"|",'Text Example'!A2))*'Table Reference'!$D$3:$D$5),4)

Fill down.