Check multiple strings in one cell against another table to match up values in Excel

Question

For my example, I have a table with one column where each cell has multiple values. To simplify I'll just use colors. In the second Column, I want a Y or N depending on what those colors say in a separate table. If any of the colors listed match up to a Y, then the result should be a Y overall.

Color	Outcome
Blue, Green, Yellow	?
Yellow, Red	?
Red, Blue	?
Red, Green	?

In my other table I have a list of each color, and Y or N in a corresponding column

Color	Y or N
Blue	Y
Yellow	Y
Red	N
Green	N

The excepted outcome for the first table should be this:

Color	Outcome
Blue, Green, Yellow	Y
Yellow, Red	Y
Red, Blue	Y
Red, Green	N

So, in Row 1, even though Green is a N, since both Blue or Yellow are a Y, then the outcome is Y.

Since you've shown no effort in your question, I won't give you a full solution. One thing I might provide is a formula you can use to check if a string (like "Blue, Green, Yellow") contains a smaller string (like "Blue" or "Red"), here it is: `=IF(SUBSTITUTE(C$4,D4,"")=C$4,"Does not contain", "Contains")`. You can use this as a starting point. The idea is that you replace the lookup value by an empty string, and check if any change has been made. — Dominique, Jun 14 '23 at 06:15

score 1 · Answer 1 · answered Jun 14 '23 at 02:12

1

Try the following formula-

=OR(XLOOKUP(TEXTSPLIT(A2,", "),$E$2:$E$5,$F$2:$F$5)="Y")

If need Y or N as output, then try-

=TAKE(SORT(XLOOKUP(TEXTSPLIT(A2,", "),$E$2:$E$5,$F$2:$F$5),,-1,TRUE),,1)

answered Jun 14 '23 at 02:12

Harun24hr

30,391
4
21
36

I unfortunately do not have the version of Excel that supports XLOOKUP, I have 2013 version. Do you know of any other formulas I could try? – C W Jun 14 '23 at 02:51
2

@CW can you try this, it is based on the solution posted by Harun Sir. But you need to hit the `CTRL + SHIFT + ENTER` --> `=OR(VLOOKUP(FILTERXML(""&SUBSTITUTE(A2,", ","")&"","//b"),$E$2:$F$5,2,0)="Y")` – Mayukh Bhattacharya Jun 14 '23 at 06:58
This partially worked, but if Red or Green (since both at N) were the first value in the cell, it would default to N overall. So if it were "Red, Yellow" it would return FALSE, but if it were "Yellow, Red" it would return TRUE. – C W Jun 14 '23 at 16:16

score 1 · Accepted Answer · answered Jun 14 '23 at 05:46

Here is a VBA solution. Because you have Excel 2013, this might prove difficult to do with built in functions. It might not be possible but I'm not 100% sure about that.

Function myMatch(ByVal inputString As String, ByVal delim As String, ByVal yonList As Range) As String
    Dim splitInput() As String, yonArr() As Variant
    splitInput = Split(inputString, delim)
    yonArr = yonList
    myMatch = "N"
    For i = 0 To UBound(splitInput)
        For j = 1 To UBound(yonArr)
            If Trim(splitInput(i)) = Trim(yonArr(j, 1)) And yonArr(j, 2) = "Y" Then
                myMatch = "Y"
                Exit Function
            End If
        Next j
    Next i
End Function

You can use this function in the worksheet like so:

=myMatch(A2;",";$E$2:$F$5)

Thanks! This worked! I did however need to change it to =myMatch(A2,",",$E$2:$F$5) — C W, Jun 14 '23 at 18:31
@CW I always forget most people use comma as argument separator. Sorry about that. — andrewb, Jun 15 '23 at 04:01

score 1 · Answer 3 · answered Jun 14 '23 at 06:25

If you have a small set of "Colors" you can use this ugly formula:

=IF(NOT(ISERROR( FIND( "Y",
   IF(NOT(ISERROR(FIND($E$2,A2))),$F$2,"") &
   IF(NOT(ISERROR(FIND($E$3,A2))),$F$3,"")&
   IF(NOT(ISERROR(FIND($E$4,A2))),$F$4,"") &
   IF(NOT(ISERROR(FIND($E$5,A2))),$F$5,"")))),
"Y","N")

It checks per color combination and per color if it occurs and returns Y or N from second table - the result is e.g. for Blue,Gree,Yellow YNY or for Red,Green NN. The outer IF then checks this result for at least one Y.

Check multiple strings in one cell against another table to match up values in Excel

3 Answers3