Trying to figure out how to search if a Number in Column A exists anywhere in Column B.

So far I've tried:

=IF(ISNUMBER(SEARCH(A2,B:B)),"YES","NO") which returns #spill 

This UDF:

Function CheckOne(rng As Range, chkValue As Long) As Boolean
    Dim n
    For Each n In Split(rng.Value, ",")
        If CLng(n) = chkValue Then
            CheckOne = True
            Exit For
        End If
    Next n
End Function

Another UDF:

Function CommaSeparatedListContains(ByVal csv As String, ByVal v As String, _
        Optional ByVal delimiter As String = ",") As Boolean
    Dim i As Long
    Dim splitCsv() As String
    splitCsv = Split(csv, delimiter)
    CommaSeparatedListContains = False
    For i = LBound(splitCsv) To UBound(splitCsv)
        If splitCsv(i) = v Then
            CommaSeparatedListContains = True
            Exit Function
        End If
    Next i
End Function

This didn't work either as I'm getting #VALUE! error

I suspect it's because the value I am looking for occurs more than once in column B.

What am I doing wrong here?

CodePudding user response：

Your example tables are minimal, and I'm not sure if it covers the entire scope.

Assuming it does, the following function should do the trick:

{=IF($A2="","-",ISNUMBER(MATCH("*,"&$A2&",*",","&$B$1:$B$7&",",0)))}

Notes:

1. The above is for a value in cell A2, using a lookup range of $B$1:$B$7.
   
2. Note the {}. This is an array formula (Enter with Ctrl, Shift and Enter)
3. The leading "return '-' if value is nothing" can be modded or removed (as appropriate)

What the formula does:

1. Leading IF($A2="","-", merely eliminates checking of blanks (returning a dash in these cases)
   • Output can be modified as appropriate (e.g. to FALSE)
   • If the checked range never has blanks, this can removed
2. ISNUMBER is used to determine if the subsequent MATCH returns a valid, non-error, value
3. MATCH is an exact match match_type = 0 with wildcards:
   • Lookup_Value is enclosed in wildcards * and commas (e.g. 2 => *,2,*)
   • Likewise, the lookup (array) values are enclosed in commas (e.g. 1,102 => ,1,102,)
     

Thus the only valid matches will be a value exactly matching within comma-separated sub-strings.

Assuming there's a more complex set of conditions and a UDF is required, here's one approach.
Note: This is intended as base code you may/may not need to expand/adapt out from.

Public Function CheckOne(rgValue As Range, rgLookup As Range) As Boolean

    Dim in1, vnSplit, rgC As Range
    
''' Compare to each cell in check range
    For Each rgC In rgLookup.Cells
    
    ''' Skip empty cells
        If rgC <> "" Then
        
        ''' Split based on comma separation (if any)
            vnSplit = Split(rgC.Value, ",")
            
        ''' No comma separation: Just compare to the value
            If UBound(vnSplit) = 0 Then
                If rgValue = rgC Then CheckOne = True: Exit Function
            
        ''' Has comma seperation: Compare to each sub-value
            Else
                For in1 = 0 To UBound(vnSplit)
                    If rgValue = Val(vnSplit(in1)) Then CheckOne = True: Exit Function
                Next in1
            End If
        End If
    
    Next rgC

End Function

CodePudding user response：

If you have access to it (i.e. Office-365 or Office 2019), then I would suggest using TEXTJOIN to "flatten" the values first. This also lets you eliminate the false-positive for values of 2 and 3 in your example, by requiring the delimiter (in this case, a comma) to precede and follow the value:

=IF(ISNUMBER(SEARCH(","&A2&",", ","&TEXTJOIN(",",True,B:B)&",")),"YES","NO")

You can also use a SUBSTITUTE to eliminate any spaces that may have been put in, such that "1, 2, 3" is treated the same as "1,2,3"):

=IF(ISNUMBER(SEARCH(","&A2&",", SUBSTITUTE(","&TEXTJOIN(",",True,B:B)&","," ",""))),"YES","NO")

However, I did discover an annoying issue when testing this: the value "100,101,102" was treated by Excel as the number One Hundred Million, One Hundred and One Thousand, One Hundred and Two (i.e. 100101102), and formatted with comma separators for the thousands — as such Excel quite forgot that the commas were supposed to exist when checking if the numbers existed in that cell! I had to change the Number Format for the cell explicitly to "Text", and re-enter the value.