方案一,利用VBA编写代码:
Sub 按钮1_Click()
For i = 2 To 5
Range("E" & i) = ""
For j = 2 To 6
If InStr(Range("B" & j), Range("D" & i)) > 0 Then
Range("E" & i) = Range("E" & i) & Space(1) & Range("A" & j)
End If
Next j
Next i
End Sub
方案二,利用公式:
先在E2单元格输入公式:
=IF(SUMPRODUCT(($A$2:$A$6=E$1)*(IFERROR(FIND($D2,OFFSET($B$1,COLUMN()-4,0,1,1),1),0)))>0,"√","")
然后再拉拽填充