Formula like Wildmatch but using data from another source

[jasontoews]

I'm working with Saleforce event data and I have a functioning formula to help me uncover certain key word phrases in the subject line and description fields of a sales event which is working fine where I have the formula (sample below) hard coded to look for 5 different keyword phrases.  

If(WildMatch(CommentColumn,'*KeyWord1*'),'Y',If(WildMatch(CommentColumn,'*KeyWord2*'),'Y','N')) ...etc

I'd like to expand this to be able to introduce a separate excel file that has a column listing as many as 40 keyword phrases that I'd like to be able to identify similar to the WildMatch formula I'm using, but I don't want to have to hard code 40 different keyword phases into the formula but to just reference the column of a separate excel sheet that the sales team can manage themselves. I did try connecting my excel sheet and tried to just insert the column name but it did not work. Does anyone know a formula I could use to make this work?

[marcus_sommer]

You don't need multiple if-loops for the above logic because wildmatch() could get multiple search-values, like:

If(WildMatch(CommentColumn,'*KeyWord1*', '*KeyWord2*', ...),'Y','N')

Instead of specifying multiple search-values you could use a variable which contains a list of the values. To get this you could load the excel and concatenating the strings per concat() including all needed kinds of quotes and wildcards. Afterwards this single string could per peek() be assigned to a variable.

Personally I would use another approach by loading all the values into a mapping-table, like:

m: mapping load SearchValues, '<<Y>>' from Excel;

and within the final load:

t:
load *, if(len(FlagY), FlagY, 'N') as Flag;
load *, textbetween(mapsubstring('m', Comment), '<<', '>>') as FlagY from Source;