Forum Discussion
Resolver IIIHow to use the Switch function to look for strings
Putting in an OR isn't a problem.
SWITCH ( TRUE (), CONTAINSSTRING ( [Title], "calorie control" ) || CONTAINSSTRING ( [Title], "cook & bake" ) , "Sucralose", CONTAINSSTRING ( [Title], "natural" ) , "Stevia", CONTAINSSTRING ( [Title], "sugar control" ) , "Aspertame" )But this isn't really any different than without the OR.
SWITCH ( TRUE (), CONTAINSSTRING ( [Title], "calorie control" ), "Sucralose", CONTAINSSTRING ( [Title], "cook & bake" ) , "Sucralose", CONTAINSSTRING ( [Title], "natural" ) , "Stevia", CONTAINSSTRING ( [Title], "sugar control" ) , "Aspertame" )Hi,
This calculated column formula works
=LOOKUPVALUE(search_terms[Result],search_terms[Search string],FIRSTNONBLANK(FILTER(VALUES(search_terms[Search string]),SEARCH(search_terms[Search string],Data[Title],1,0)),1))The search terms table looks like this
Search stringResult
Calorie control Sucralose Cook & bake Sucralose natural Stevia sugar control Aspertame Hope this helps.
The SWITCH function returns the first result that matches the first argument (in this case, TRUE). So, in this pattern, it returns the result following the first test that evaluates as being true.
More detail here:
https://p3adaptive.com/2015/03/the-diabolical-genius-of-switch-true/
Resolver III| Title | MPU | Active |
| Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 50 Sachets, Pack of 5 | 5 | Aspertame |
| Equal Stevia Natural Sweetener, Sugar Free, 100 Sachets - Pack of 1 | 1 | Stevia |
| Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 50 Sachets, Pack of 3 | 3 | Aspertame |
| Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Sachets, Pack of 4 | 4 | Aspertame |
| Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Sachets, Pack of 2 | 2 | Aspertame |
| Equal Sweetener, Sugar Free, Zero Calorie, Cook & Bake, 80g Powder Jar, Pack of 1 | 1 | |
| Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 300 Tablets, Pack of 2 | 2 | Aspertame |
| Equal Stevia Natural Sweetener, 100 Tablets - Pack of 2 | 2 | Stevia |
| Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 100 Sachets, Pack of 6 | 6 | |
| Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Sachets, Pack of 6 | 6 | Aspertame |
| Equal Stevia Natural Sweetener, Sugar Free, 50 Sachets - Pack of 2 | 2 | Stevia |
| Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 300 Tablets, Pack of 2 | 2 | |
| Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 300 Tablets, Pack of 3 | 3 | Aspertame |
| Equal Stevia Natural Sweetener, Sugar Free, 100 Sachets - Pack of 2 | 2 | Stevia |
| Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Sachets, Pack of 3 | 3 | Aspertame |
| Equal Stevia Natural Sweetener, 100 Tablets - Pack of 4 | 4 | Stevia |
| Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 100 Sachets, Pack of 2 | 2 | |
| Equal Sweetener, Sugar Free, Low Calories, Sugar Control, 100 Tablets + 10 Tablets Free Tablets, Pack of 3 | 3 | Aspertame |
| Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 100 Tablets, Pack of 6 | 6 | |
| Equal Sweetener, Sugar Free, Zero Calorie, Calorie Control, 100 Tablets, Pack of 3 | 3 |
The column called [active] is the target column. The column to search thru' is the 1st column called [Title].
Does this work for you?
To answer the question you have asked at the end - that is precisely the problem I am trying to solve for. Which means there has to be a way to use a kind of OR function, where the search can happen across multiple strings at the same time.
E.g. If [Title] contains "Calorie control" OR "Cook & bake", Then return "Sucralose"
Hope I have been able to explain a little more clearly for you.
Appreciate your time and help
Super UserPutting in an OR isn't a problem.
SWITCH (
TRUE (),
CONTAINSSTRING ( [Title], "calorie control" ) || CONTAINSSTRING ( [Title], "cook & bake" ) , "Sucralose",
CONTAINSSTRING ( [Title], "natural" ) , "Stevia",
CONTAINSSTRING ( [Title], "sugar control" ) , "Aspertame"
)But this isn't really any different than without the OR.
SWITCH (
TRUE (),
CONTAINSSTRING ( [Title], "calorie control" ), "Sucralose",
CONTAINSSTRING ( [Title], "cook & bake" ) , "Sucralose",
CONTAINSSTRING ( [Title], "natural" ) , "Stevia",
CONTAINSSTRING ( [Title], "sugar control" ) , "Aspertame"
)- monojchakrab
This is a very elegant solution AlexisOlson .
I was wondering what we need the TRUE() for? I have seen this used with the SWITCH function in a number of other instances, but never really understood why.
Would you be able to throw some light on that?
- AlexisOlson
The SWITCH function returns the first result that matches the first argument (in this case, TRUE). So, in this pattern, it returns the result following the first test that evaluates as being true.
More detail here:
https://p3adaptive.com/2015/03/the-diabolical-genius-of-switch-true/