first and last text match from list

(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:

Please Login or Register  to view this content.

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.

There are several ways to do this. Here's just one.

With list of words in A2:A8 and lookup segment (frn) in B1 this in B2 and filled across C2.

Formula:

Please Login or Register  to view this content.

Match can match multiple items and multiple match_types simultaneously. {0,1}matches the first and last occurrences of 1 in the array returned by
1/($B$1=LEFT($A$2:$A$8,LEN($B$1))).

The {1,2} looks up the first occurrence of 1 and the rightmost number less than or equal to 2.

LOOKUP always uses the Approximate Match version that you would get when using TRUE at the end of your VLOOKUP. It'll return the LAST value that is nearst (less than) the search value. Ultimately, as you noted, the formula returns 1,#VALUE,#VALUE,1... It looks for the last value that is nearest to 2. Clearly, it doesn't have to be 2. I can be anything >=1. Most people choose 2 ' cos it's a nice number...

Also LOOKUP is one of the few Excel functions that ignores errors ... as does the approximate MATCH in the explanation post #2.