|
发表于 2017-7-26 12:49
|
显示全部楼层
本楼为最佳答案
本帖最后由 cabcyvr 于 2017-7-26 12:51 编辑
[img][/img] B2的公式是数组,要 CONTROL SHIFT ENTER 3键一起按压执行,之后复制到其他单元格内。直接按ENTER执行没有用的。
Sheet2
B2=IFERROR(INDEX(Sheet1!$C:$C,SMALL(IF((Sheet1!$D$2:$D$1000>=SUBSTITUTE(LEFT($A2,2),"~",)-0)*(Sheet1!$D$2:$D$1000<=SUBSTITUTE(MID($A2,3,9),"~",)-0)*(Sheet1!$A$2:$A$1000=B$1),ROW(Sheet1!$A$2:$A$1000),"/"),1))&"("&IFERROR(INDEX(Sheet1!$B:$B,SMALL(IF((Sheet1!$D$2:$D$1000>=SUBSTITUTE(LEFT($A2,2),"~",)-0)*(Sheet1!$D$2:$D$1000<=SUBSTITUTE(MID($A2,3,9),"~",)-0)*(Sheet1!$A$2:$A$1000=B$1),ROW(Sheet1!$A$2:$A$1000),"/"),1)),"")&")","") |
|
|