Forum Discussion
excel formula to return text string that matched cells in named range
The first match is given by the array formula (confirmed with Ctrl+Shift+Enter):
=INDEX(Notsubs,MATCH(TRUE,ISNUMBER(SEARCH(Notsubs,N12)),0))
If there could be multiple matches, they are returned separated by commas by the array formula (confirmed with Ctrl+Shift+Enter):
=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH(Notsubs,N12)),Notsubs,""))
HansVogelaaris there a way for the search to return results that are only an exact match in the named range? For example in the table is Ring West and if the cell N12 contains Ring, it returns Ring West. I only want it to match Ring West (not Ring or West separately). Is this possible?
Thanks!
That would be
=VLOOKUP(N12,Notsubs,1,FALSE)
but this would simply return the value of N12 (unless not found, then it returns #N/A)
HansVogelaarHi, thank you but that wont work as the cells I am searching through have many other words in there...
I'm afraid I don't understand what you want.
Could you attach a sample workbook (without sensitive information) with a few examples of what you expect?
More formal business logic is to be defined. For example, if you have defined list of words to lookup, it could be the rule not to return texts in which more than one word from the list. With this, if Ring and West are in such list, Ring West will be ignored, but Ring only, West only or Gold Ring - not. Perhaps that could be another logic, but some shall be defined to convert it to formulas.
