5,172
社区成员




Function HeBing(rng1 As Range, s As String, rng2 As Range, f As String) As String
Dim Arr1, Arr2
Dim r As Long
r = rng1.End(xlDown).Row - rng1.Row + 1
Arr1 = rng1.Resize(r, 1): Arr2 = rng2.Resize(r, 1)
Dim i As Long
For i = 1 To UBound(Arr1)
If Right(Arr1(i, 1), 3) = Right(s, 3) Then
If HeBing = "" Then HeBing = Arr2(i, 1) Else HeBing = HeBing & f & Arr2(i, 1)
End If
Next
End Function
在C列填入公式假定是使第2行
=HeBing(B:B,A2,B:B,",")
--------
如果您只要找一个,或 您的 Excel 没有安装宏编辑器
你可以在 B列前插入一列 B2填入公式
=RIGHT(C2,3)
先计算目标的后三位,目标已移动到C列然后再D2填入公式
=VLOOKUP(RIGHT(A2,3),B:C,2,FALSE)