My question is similar to this question posted previously. My data is organized in the following manner:
- Column A (Outcome) – contains user entered text describing an outcome. This can be of varying lengths.
Example: On March 2, we found a green scarf with fringes. No one came forward to claim this scarf so we left it in the bin for 10 days. After 10 days, it was donated to a shlter.
- Column D (search terms) – contains a list of words to look for in the Outcome column.
Example: scarf, mitten, hat, shelter
-
Column B (Match) – checks to see if a word/words from Column D are within the outcome text. This is a true/false field. I’m using this formula:
=XLOOKUP(TRUE,(ISNUMBER(SEARCH(D:D,A2))),D:D,”false”)
Example: This would be True because it contains “scarf”.
My question is this: In the above example, “shelter” appears as a search term in Column D. You’ll notice that the word “shelter” is spelled wrong in Column A. If “scarf” was not in the list as well, Column C would have returned “False”. Is there a way that Excel can also identify similar spelling so that “shlter” would have been recognized as “shelter” and therefore marked as “True”?
I’ve tried using the Fuzzy Lookup add-on but it just returns 0. I suspect this is because Column A contains such a long string that any matches it does find just get reduced to 0.
I’m using Microsoft365, but I recognize this problem might beyond Excel so any suggestions would be greatly appreciated.
