kimmymckenzie
New Member
- Joined
- Jun 18, 2018
- Messages
- 1
{=IFNA(INDEX(NamedRange,MATCH(TRUE,ISNUMBER(SEARCH(NamedRange2,CellcontaingStringoftext)),0))’””)}
This array is looking a up list of words that could be included anywhere in the string of text and returns the corresponding value of next column of the first identified substring, if it does not successfully match it returns nothing.
My concern with this array is that it returns only the first value.
In the perfect world I would have it List all of the corresponding values, though, at a minimum, I would like it to identify if it finds one (and return it) or to return “multiple” when it finds more than one substring in the string of text.
I can’t share a sample file because the site is restricted on my work computer, I’m working on this! But what I can say is that the list of substring is common words to describe frontline IT issues, and the corresponding value is the system name. The string of text that it’s looking up is the description that is written in an IT incident / tickets.
Any suggests would be greatly appreciated!
Kim
This array is looking a up list of words that could be included anywhere in the string of text and returns the corresponding value of next column of the first identified substring, if it does not successfully match it returns nothing.
My concern with this array is that it returns only the first value.
In the perfect world I would have it List all of the corresponding values, though, at a minimum, I would like it to identify if it finds one (and return it) or to return “multiple” when it finds more than one substring in the string of text.
I can’t share a sample file because the site is restricted on my work computer, I’m working on this! But what I can say is that the list of substring is common words to describe frontline IT issues, and the corresponding value is the system name. The string of text that it’s looking up is the description that is written in an IT incident / tickets.
Any suggests would be greatly appreciated!
Kim