如何在含数组与常量混合的ID列中查找对应VALUE（禁止拆分数组行）

问题概述 #

现有ID与VALUE对应数据，规则为：单个ID直接存储，多个同VALUE的ID以=@{ID1,ID2,...}数组格式存储，数据示例如下：

需要实现：不拆分数组ID为单独行，查找数组内ID（如0003）对应的VALUE。当前已能用LOOKUP处理单个ID，公式为：

=IFERROR(LOOKUP(VALUE([@CCC]),'Sheet1'!$B$4:$C$541),[查找数组内ID的方法])

同时已有一个判断ID是否在数组中的VBA函数：

Public Function IsInArray(stringToBeFound As Integer, arr As Variant) As Boolean
    Dim i
    For i = LBound(arr) To UBound(arr)
        If arr(i) = stringToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next i
    IsInArray = False
End Function

解决方案1：纯Excel公式实现 #

将原公式中的[查找数组内ID的方法]替换为以下公式，即可覆盖数组ID的查找：

INDEX('Sheet1'!$C$4:$C$541,SUMPRODUCT(--(ISNUMBER(SEARCH(","&TEXT([@CCC],"0000")&",",","&SUBSTITUTE(SUBSTITUTE('Sheet1'!$B$4:$B$541,"=@{",""),"}","")&",")))*ROW('Sheet1'!$B$4:$B$541))-ROW('Sheet1'!$B$4)+1)

公式逻辑： #

1. SUBSTITUTE(SUBSTITUTE(...))：把数组格式的ID字符串=@{0002,0003}转换为纯ID列表0002,0003
2. 给目标ID和转换后的ID列表前后加逗号，避免部分匹配（比如000匹配0001）
3. SEARCH+ISNUMBER判断目标ID是否存在于当前行的ID列表中
4. SUMPRODUCT计算符合条件的行号，INDEX返回对应行的VALUE

解决方案2：改进VBA函数实现 #

编写一个直接完成查找的VBA函数，比公式更直观高效：

Public Function FindValueByID(targetID As String, idRange As Range, valueRange As Range) As Variant
    Dim cell As Range
    Dim idContent As String
    Dim idArray As Variant
    Dim idx As Integer
    
    For Each cell In idRange
        idContent = cell.Value
        ' 判断当前单元格是否为数组格式ID
        If Left(idContent, 3) = "=@{":
            ' 提取并拆分数组内的ID
            idContent = Mid(idContent, 4, Len(idContent) - 4)
            idArray = Split(idContent, ",")
            ' 遍历数组匹配目标ID
            For idx = LBound(idArray) To UBound(idArray)
                If Trim(idArray(idx)) = targetID Then
                    FindValueByID = valueRange.Cells(cell.Row - idRange.Row + 1).Value
                    Exit Function
                End If
            Next idx
        Else
            ' 单个ID直接匹配
            If idContent = targetID Then
                FindValueByID = valueRange.Cells(cell.Row - idRange.Row + 1).Value
                Exit Function
            End If
        End If
    Next cell
    
    ' 未找到匹配项时返回空值
    FindValueByID = ""
End Function

使用方式： #

在单元格中调用函数，确保目标ID为带前导零的字符串格式：

=FindValueByID(TEXT([@CCC],"0000"),'Sheet1'!$B$4:$B$541,'Sheet1'!$C$4:$C$541)

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