Forum Discussion
How 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/
| 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
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.
Very interesting solution Ashish_Mathur .
Will check this out
Thank you.