# Formula for finding longest string match in a cell from a list and returning the matching string

In an Excel workbook, I have a formula that searches for matches to a cell from a list. It works, but doesn't find the longest string match. How do I modify the formula so it returns the longest matching string? For example, if there are multiple matching strings to a cell that contains "CD27.2", it returns "CD27" and not "CD27.2". See below for formula:

``````=INDEX(list,MATCH(TRUE,ISNUMBER(SEARCH(list,[@[Cell]])),0))
``````

I'd like to avoid macros/VBA.

• Order the lookup list by length of the string descending. You can use a helper column with the length of the string then sort on that descending. – Scott Craner Apr 22 '19 at 15:25
• That's why you would order them by length of string descending. – cybernetic.nomad Apr 22 '19 at 15:36
• Sorry - see what you meant so deleted comment. Would be great to know if there's a way to address this from the formula if possible. – user77875 Apr 22 '19 at 15:36
• If you want people to work on a specific solution, post an example with some sample data as a text table that they can copy and paste. Have it reflect the patterns you're working with. – fixer1234 Apr 22 '19 at 22:05

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.

How it works:

1. Enter CD the criteria in Cell `A1`.
2. An Array (CSE) Formula in cell `B3`, finish with Ctrl+Shift+Enter & fill down.

``````{=IFERROR(INDEX(\$A\$3:\$A\$9, SMALL(IF(ISNUMBER(SEARCH(\$A\$1, \$A\$3:\$A\$9)), MATCH(ROW(\$A\$3:\$A\$9), ROW(\$A\$3:\$A\$9))), ROWS(\$A\$1:A1))),"")}
``````

N.B.

1. If in Cell `A1` value is either `CD` or `CD27`, you get both `CD27` & `CD27.2`, but if `A1` has `CD27.2` then Formula will returns only `CD27.2` .

Adjust cell references in the Formula as needed.

• Write your concern,, to down voted this post, help me & community also !! – Rajesh S Apr 25 '19 at 7:26