如何在Excel逗号分隔值列中查询指定数字是否存在

错误原因 #

• 原生公式=IF(ISNUMBER(SEARCH(A2,B:B)),"YES","NO")返回#SPILL错误是因为SEARCH(A2,B:B)会为B列每一行生成对应结果，输出的数组超出了单个单元格的承载范围，触发溢出错误。
• 两个UDF返回#VALUE!错误的原因：一是传入了整个B列作为参数，原UDF仅支持单个单元格输入；二是B列存在空单元格，Split函数处理空值时会触发类型不匹配错误。

方案1：原生公式（无需启用宏） #

Excel 365/2021及以上版本 #

在C2单元格输入以下公式，下拉填充即可：

=IF(SUM(--ISNUMBER(SEARCH(","&A2&",",","&B:B&","))),"YES","NO")

前后拼接逗号是为了避免部分匹配，比如防止A列的1误匹配到B列的14、21这类数值

Excel 2019及更早版本 #

用SUMPRODUCT适配数组运算：

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(","&A2&",",","&B:B&",")))>0,"YES","NO")

方案2：修正后的UDF方案 #

替换原有UDF代码，新增空值容错、空格清理和多单元格适配：

Function CheckExists(checkVal As Long, searchRng As Range) As Boolean
    Dim cell As Range
    Dim arr As Variant
    Dim i As Long
    For Each cell In searchRng
        If cell.Value <> "" Then
            arr = Split(cell.Value, ",")
            For i = LBound(arr) To UBound(arr)
                If Trim(arr(i)) = CStr(checkVal) Then
                    CheckExists = True
                    Exit Function
                End If
            Next i
        End If
    Next cell
    CheckExists = False
End Function

使用方法：在C2单元格输入以下公式，下拉填充即可：

=IF(CheckExists(A2,B:B),"YES","NO")

内容的提问来源于stack exchange，提问作者h2o