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