Check id every value in ";" separated string exists on a LOV list using just Excel formulas

Question

In a cell I have a multi value separated by semicolon like this: Red;Blue;Green I need to compare if each of those values exist on a list: Black Orange Green Blue Red I think it should be an array formula, but I have no idea how to set it. Is it even possible? Regards Michał

Answer 1

You've not mentioned what output you are looking for. Below are the two possible solution.

1. If you are looking for the count of words in a cell from the list use following formula:

=SUMPRODUCT(ISNUMBER(FIND($E$2:$E$6,$A2))*1)

2. If you want words in the cell that are in the list to be displayed in separate columns, use the following array formula

=IFERROR(INDEX($J$2:$J$6,SMALL(IF(ISNUMBER(FIND($J$2:$J$6,$A2)),ROW($J$2:$J$6)-ROW($J$1)),COLUMNS($A1:A1))),"")

Drag/copy above formula across and down as required. Being an array formula you'll have to commit this formula by pressing Ctrl+Shift+Enter.

Answer 2

You can write this UDF and use it as a formula. Wasn't sure what output is required. This UDF gives number of items that match in the list.

Parameters:

myValue - the cell that contains multi value separated by semicolon

listRange - Range that has the list to check against. Should be a single column list

Function checkList(myValue As Range, listRange As Range) As Integer
    Dim t As Variant
    t = Split(myValue.Value, ";")

    Dim c As Integer
    c = 0
    For i = LBound(t) To UBound(t)
        For j = 1 To listRange.Rows.Count
            If (t(i) = listRange(j, 1)) Then
                c = c + 1
            End If
        Next j
    Next i

    checkList = c
End Function

Answer 3

Since you want to do this only with excel formulas, the input string has to be split to multiple cells before comparing it with the list.

If your input string is in A1, use the below formula and drag it right to split them based on the delimiter ;.

=TRIM(MID(SUBSTITUTE($A1,";",REPT(" ",999)),1+((COLUMN(A1)-1)*999),999))

Assuming your list is in column G, use the below formula which counts the strings Red, Blue and Green in your list and returns Found or Not found.

in C2,

=IF(COUNTIF($G:$G,C1),"Found","Not found")

Hope this helps.