(I have excel 2013)
I have a list of words in a column (also in a named range), sorted alphabetically.
I need to find the first and last words that begins with a chosen string
For example, if I have this list of words:
afrn
frghy
frntg
frntgh
frntjdf
froty
zfrn
if my string to match is "frn", the first and last matches are "frntg" and "frntjdf"
I have this array function to find the first match:
{=INDEX(named_range,MATCH(TRUE,ISNUMBER(SEARCH(E3,named_range)),0))}
but it finds "afrn" instead of "frntg" (I want the matched words to start with "frn")
and I don't know how to find the last match.
Bookmarks