Forum Discussion
Solved
excel formula to return text string that matched cells in named range
I have the below formula which is searching through a named range (Notsubs) to see if there a partial match in cell N12. Formula is working. What I want to see now is what it matched - how do I ...
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,""))
