准提部林

B2:
=IF(COUNT(FIND(A2,E$1)),A2,"")

C2:
=IFERROR(LEFT(VLOOKUP("*"&A2&"*",E$1:E$2,1,),3),"")

准提部林

回復 3# leiru

=IF(COUNT(FIND("+"&A2&"+","+"&MID(E$1,5,199)&"+")),A2,"")

=iferror(LEFT(LOOKUP(1,-FIND("+"&A2&"+","+"&MID(E$1:E$2,5,199)&"+"),E:E),3),"")

准提部林

回復 5# leiru


怎麼帶, 模擬結果看看~~

准提部林

回復 7# leiru


B2/陣列:
=IF(COUNT(FIND("+"&A2&"+","+"&SUBSTITUTE(E$1:E$2,"=","+")&"+")),A2,"")

C2/一般:
=IFERROR(LEFT(LOOKUP(1,-FIND("+"&A2&"+","+"&SUBSTITUTE(E$1:E$2,"=","+")&"+"),E:E),3),"")

============================================
若將字串改成:
XSV+/+XSW+XSX+XSY+XSZ+XT0+XT1+XUX+XV0+XV1+XV2
XT3+/+XT4+XT5+XT6+XT7+XT8+XT9+XUY+XV3+XV4+XV5

=IF(COUNT(FIND("+"&A2&"+","+"&E$1:E$2&"+")),A2,"")
=IFERROR(LEFT(LOOKUP(1,-FIND("+"&A2&"+","+"&E$1:E$2&"+"),F:F),3),"")


============================================

准提部林

回復 9# leiru


高版本的有合併字串函數, 我沒有!
低版本要用VBA

准提部林

回復 11# leiru

Sub TEST()
Dim R&, Arr, T$, TT$, TS, xD, i&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([D1], [D65536].End(xlUp)(2))
For i = 2 To UBound(Arr)
    If Arr(i, 1) <> "" Then xD(Arr(i, 1) & "") = ""
Next i
Arr = Range([A2], [A65536].End(xlUp)(2))
For i = 1 To UBound(Arr)
    TT = "": T = Replace(Arr(i, 1), "=", "+")
    If T = "" Then GoTo 101
    For Each TS In Split(T, "+")
        If TS <> "" And xD.Exists(TS & "") Then TT = TT & "、" & TS
    Next
    Arr(i, 1) = Mid(TT, 2)
101: Next i
[B2].Resize(UBound(Arr)) = Arr
End Sub


==================================