合并LookupSP与RemoveDupes2 UDF 解决Excel字符限制问题

合并后的自定义函数（UDF） #

以下是将LookupSP的多值查找功能与RemoveDupes2的去重功能合并后的VBA代码，直接在查找过程中完成去重，避免生成超大中间字符串导致的字符限制错误：

Function LookupSPWithDedupe(lookupValue As String, lookupRange As Range, _
                           lookupCol As Integer, returnCol As Integer, _
                           lookupDelim As String, returnDelim As String, _
                           errorValue As String) As String
    Dim lookupArr As Variant
    Dim resultCol As Range
    Dim tempResult As String
    Dim resultSet As Object
    Dim i As Integer
    Dim cellValue As String
    Dim matchFound As Boolean
    
    ' 初始化去重集合
    Set resultSet = CreateObject("Scripting.Dictionary")
    
    ' 拆分查找单元格中的多值
    lookupArr = Split(lookupValue, lookupDelim)
    
    ' 获取返回列的范围
    Set resultCol = lookupRange.Columns(returnCol)
    
    ' 遍历每个查找值
    For i = LBound(lookupArr) To UBound(lookupArr)
        cellValue = Trim(lookupArr(i))
        If cellValue <> "" Then
            ' 在查找列中匹配值
            On Error Resume Next
            matchFound = Not IsError(Application.Match(cellValue, lookupRange.Columns(lookupCol), 0))
            On Error GoTo 0
            
            If matchFound Then
                ' 获取对应返回值
                tempResult = Application.VLookup(cellValue, lookupRange, returnCol, False)
                ' 若返回值未在集合中，则添加
                If Not resultSet.Exists(tempResult) Then
                    resultSet.Add tempResult, tempResult
                End If
            End If
        End If
    Next i
    
    ' 拼接去重后的结果
    If resultSet.Count > 0 Then
        LookupSPWithDedupe = Join(resultSet.Keys, returnDelim)
    Else
        LookupSPWithDedupe = errorValue
    End If
    
    ' 释放对象
    Set resultSet = Nothing
End Function

代码说明 #

1. 去重逻辑嵌入查找流程：用Scripting.Dictionary记录已找到的返回值，每匹配到一个结果先检查是否已存在，不存在才加入集合，从源头避免重复内容积累。
2. 规避字符限制：不再先拼接所有查找结果再去重，而是边查找边去重，大幅降低中间阶段的字符串长度，解决Excel单元格32676字符上限触发的#VALUE!错误。
3. 参数完全兼容：参数与原LookupSP一致，无需调整公式结构，直接替换原嵌套公式即可。

使用方法 #

将原嵌套公式：

=RemoveDupes2(LookupSP(B2,$A$9:$B$11,1,2,",",",","ERROR"),",")

替换为：

=LookupSPWithDedupe(B2,$A$9:$B$11,1,2,",",",","ERROR")

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