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 ...

Formulas and Functions

HansVogelaar
Sep 09, 2020
Elizabeth1744

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,""))

Elizabeth1744

Copper Contributor

Sep 11, 2020

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!

HansVogelaar

MVP

Sep 11, 2020

Elizabeth1744

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)

Forum Discussion

excel formula to return text string that matched cells in named range

Resources