You say that you want to find the longest string match.
That terminology is somewhat ambiguous.
I’ll interpret it to mean “the longest string that matches
(in its entirety).”

It seems to me that the first step
in finding the longest string that matches
is to find *the ***length of** the longest string that matches.

I’ll assume that the strings that you want to search *for*
are in Column `L`

(i.e., the “list”),
and that the strings that you want to search *in*
are in Column `A`

, starting in Row 1 in both columns.
(See example below.)
We can get the length of the longest string that matches by entering

```
=MAX(IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0))
```

as a helper column (for example, Column `B`

).
This and all other formulas in this answer are array formulas;
you must press `Ctrl`+`Shift`+`Enter`
(a.k.a. CSE) after entering them,
and so they will appear in the formula bar
with braces (`{`

and `}`

) around them.
In my sample data (below), for Row 1
(where `A1`

contains “Once upon a midnight dreary,”),
this creates a virtual array
that looks like {0,0,6,0,0,0,0,0,0,0,5,0,…}, because
`A1`

contains “dreary” (`L3`

) and “night” (`L11`

),
whose lengths are 6 and 5, respectively.
Then it takes the maximum number from that array, getting 6
(the length of “dreary”, the longest matching string).

Then we use a technique similar to the one you attempted: put

```
=INDEX(L$1:L$99,MATCH(B1,IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0),0))
```

into `C1`

, press CSE, and drag/fill down.
This recreates the same virtual array ( {0,0,6,0,0,0,0,0,0,0,5,0,…} ),
searches it for 6 (the value of `B1`

), and finds it in the third member.
Then it uses 3 as an index into the keyword list and gets “dreary”,
which is, indeed, the six-letter word
that appears in “Once upon a midnight dreary”.

We can eliminate the helper column
simply by inserting the formula for `B1`

into the `C1`

formula shown above:

```
=INDEX(L$1:L$99,MATCH(MAX(IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0)),IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0),0))
```

this is what I have in Column `D`

in my example.

Row 4 demonstrates an exact analogy to case you cite in your question:
`A4`

is “volume of forgotten lore;”
which contains both “forgot” (`L4`

) and “forgotten” (`L5`

).
The formulas find “forgotten” (the longer one).

Note that the order of the keyword list is still important
(as it was in your attempt).
`A5`

(“while I nodded, nearly napping,”) contains
both “nodded” (`L12`

) and “nearly” (`L9`

).
They are both 6 characters long, so `B5`

is 6,
and so `C5`

and `D5`

show “nearly”,
because it appears in the keyword list (Column `L`

) before “nodded”.

If your “search in” strings always contain
at least one of your “search for” strings, this should be good enough.
But notice cell `A6`

(“suddenly there came a tapping,”),
which contains none of the keywords.
This results in `B6`

= 0, and `INDEX`

stupidly treats that as a 1
and reports “curious”, the first word in the keyword list.
We protect against this with

```
=IF($B1=0, "", INDEX(L$1:L$99,MATCH(B1,IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0),0)))
```

in Column `E`

, which explicitly handles the case of a zero in Column `B`

.
If you want something to appear other than a blank field
(e.g., an error message), put that inside the quotes (after `$B1=0,`

).

## TL;DR

If you need to do this all in one column (with no helper column), use

```
=IF(MAX(IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0))=0, "", INDEX(L$1:L$99,MATCH(MAX(IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0)),IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0),0)))
```

(with `Ctrl`+`Shift`+`Enter`).
This is the Column `E`

formula from above,
with the formula for `B1`

embedded (twice),
and is shown in Column `F`

in the illustration.
If you’re willing to use a helper column, define it as

```
=MAX(IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0))
```

and get your result with

```
=IF($B1=0, "", INDEX(L$1:L$99,MATCH(B1,IF(ISNUMBER(SEARCH(L$1:L$99,$A1)),LEN(L$1:L$99),0),0)))
```

replacing `B1`

with your helper column, if necessary, and using CSE for both.

P.S. Click on edit to get my test data as text.

length of stringdescending. – cybernetic.nomad Apr 22 '19 at 15:36