Lookup or Join based on partial strings and values in multiple columns

[Anonymous]

Hi folks. 

I can figure no smart way to do this. Sure you guys have brilliant ideas.

I have an Action table in which each action has a action name, an action type and an action category. Each field is a string. The value of Action Name can be any string, the value of Action Type is one of a few possible strings, the value of Action Category is one of a few (other) possible strings.This table has about a thousand rows.

I have a Tag table which I would like to use to assign one tag to each action. Each row of the Tag table shows criteria that must be met for an action to qualify. The three criteria columns are Action name beginning, Action type, Action category. This table has two dozen rows. Multiple rows can have the same tag value. Any criterion can be null in a given row.

Criteria are such that an action will never match more than one Tag row but it could match none, in which case it will be tagged "Other".

Of course I could write custom columns in the Action table and hard-code the criteria, but there must be a more fexible approach, right?

Many. Thanks

[v-shex-msft · Accepted solution]

Hi @Anonymous,

>The three criteria columns are Action name beginning, Action type, Action category. This table has two dozen rows. Multiple rows can have the same tag value. Any criterion can be null in a given row.

I'd like to suggest you add a calculated column with if statement and text functions to combine multiple column values to one with conditions.

Please share some sample data with expect result to help us clarify your requirement.

Regards,
Xiaoxin Sheng

[v-shex-msft · Accepted solution]

Hi @Anonymous,

>The three criteria columns are Action name beginning, Action type, Action category. This table has two dozen rows. Multiple rows can have the same tag value. Any criterion can be null in a given row.

I'd like to suggest you add a calculated column with if statement and text functions to combine multiple column values to one with conditions.

Please share some sample data with expect result to help us clarify your requirement.

Regards,
Xiaoxin Sheng

[Anonymous · Accepted solution]

Thank you. I have finally decide to simply add a calculated column with multiple 'if... then... else... " statements. Not the cleanest way but the simplest to code and reda anyway. Thank you for your suggestion.