excel中遍历与同行其他列内容相同的其他sheet中的A列并获取对应行B列内容

自定义函数如下

Function sjxmj(compareCell, compareArea, valSourceArea)
     Dim DataRange As Variant
     Dim DataRangeB As Variant
     Dim Irow As Long
     Dim MaxRows As Long
     Dim Icol As Integer
     Dim MaxCols As Long
     Dim MyVar
    
     sjxmj = ""
     DataRange = compareArea.CurrentRegion.Value
     DataRangeB = valSourceArea.CurrentRegion.Value
     MaxRows = compareArea.CurrentRegion.Rows.Count
     For Irow = 1 To MaxRows
             MyVar = DataRange(Irow, 1)
             If MyVar = compareCell Then
                 sjxmj = sjxmj + "," + DataRangeB(Irow, 1)
             End If
     Next Irow
     If Len(sjxmj) > 0 Then
        sjxmj = Mid(sjxmj, 2)
     End If
End Function

调用方法如下

=sjxmj(C9,'Backlog Detail'!J$14:J$288,'Backlog Detail'!E$14:E$288)