CODEWITHSUNDEEP
excelstringexcel-formulasubstringmatch
Axel
Axel

Reputation: 2725

Excel Formula - Match substrings of List to List

I have two Lists in an excel spreadsheet.

The first list has strings such as

1234 blue 6 abc
xyz blue/white 1234
abc yellow 123

The other list contains substrings of the first list

yellow
blue/white
blue

Result

1234 blue 6 abc         blue
xyz blue/white 1234     blue/white
abc yellow 123          yellow

Now I need some kind of match formula to assign the correct value from the second list to the first. The problem, there is no specific pattern to determine where the color substring is positioned. The other problem, the values are not totally unique. As my example above shows, the lookup needs to be in an order (checking for "blue/white" before checking for "blue").

I played around the formulas like match, find also using wildcards * but couldn't come to any result. A similar question asked here on SO covers the opposite case How to find if substring exists in a list of strings (and return full value in list if so)

Any help is appriciated. A formula would be cool, but using vba is also okay.

Upvotes: 1

Views: 1778

Answers (2)

Axel
Axel

Reputation: 2725

Here is a solution with VBA

  • List 1 (strings) is in column A
  • List 2 (substrings) is in column C

The code basically contains to nested while loops checking whether the substring is inside the string.

row_1 = 1
While .Cells(row_1, "A") <> ""
    row_2 = 1
    While .Cells(row_2, "C") <> ""
        color = .Cells(row_2, "C").Value
        If InStr(1, .Cells(row_1, "A"), color, vbBinaryCompare) > 0 Then
            .Cells(row_1, "B") = color
        End If       
        row_2 = row_2 + 1
    Wend
    row_1 = row_1 + 1       
Wend

Upvotes: 0

user10779473
user10779473

Reputation: 

=INDEX(D$7:D$9, AGGREGATE(15, 7, ROW($1:$3)/ISNUMBER(SEARCH(D$7:D$9, A2)), 1))

enter image description here

Upvotes: 5

Related Questions