0

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.

  • 2
    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
  • 1
    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
0

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,).

spreadsheet with sample data, demonstrating formulas

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.

|improve this answer|||||
-1

enter image description here

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.