D5:E16{=TRIM(MID(SUBSTITUTE(INDEX(A:A,SMALL(IF(LEN($B$1:$B$4)-LEN(SUBSTITUTE($B$1:$B$4,CHAR(10),))+1>=COLUMN($A:$I),ROW($1:$4),9),ROW(A1)))&"",CHAR(10),REPT(" ",99)),MAX(1,COUNTIF(C$5:C5,C5))*99-98,99))
G5:G16{=INDEX(A:A,SMALL(IF(LEN($B$1:$B$4)-LEN(SUBSTITUTE($B$1:$B$4,CHAR(10),))+1>=COLUMN($A:$I),ROW($1:$4),9),ROW(A1)))&""
H5:H16=IF(G5="","",TRIM(MID(SUBSTITUTE(VLOOKUP(G5,A:B,2,),CHAR(10),REPT(" ",99)),COUNTIF(G$5:G5,G5)*99-98,99)))
留言
張貼留言