Forum Discussion
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 see which text from the named range that was matched in 12?
=SUMPRODUCT(--ISNUMBER(SEARCH(Notsubs,N12)))>0
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,""))
13 Replies
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)
HansVogelaarPerfect! Thank you!!
