求大神有木有更快的方法达到我想

我来帮你@硫酸下

可以用代码的话帮你写了个自定义函数。基本上格式你可以任性变
用法adx(要找的值,原区域,结果在原区第几列) 参照图上E2公式
Function adx(x, y As Range, z)
b = y.Columns.Count
a = ActiveSheet.UsedRange.Rows.Count
If y.Rows.Count > 1040000 Or y.Rows.Count > a Then
ar = y.Resize(a, b)
Else
ar = y
a = UBound(ar)
End If
For i = 1 To a
If ar(i, 1) = "" Then
ar(i, 1) = ad
If ad = x Then
If k = "" Then
k = ar(i, z)
Else
k = k & "," & ar(i, z)
End If
End If
Else
ad = ar(i, 1)
If ad = x Then
If k = "" Then
k = ar(i, z)
Else
k = k & "," & ar(i, z)
End If
End If
End If
Next
adx = k
End Function