本帖最后由 AmoKat 于 2017-10-23 18:11 编辑
=IFERROR(INDEX(Sheet1!B:B,SMALL(IF(AND(Sheet1!B8:C52<>"",FIND("柜",Sheet1!B8:C52)),ROW(Sheet1!B8:C52)),ROW(A1))),"")
改為
=IFERROR(INDEX(Sheet1!B:B,SMALL(IF((Sheet1!B8:C52<>"")*ISNUMBER(FIND("柜",Sheet1!B8:C52)),ROW(Sheet1!B8:C52)),ROW(A1))),"")
上式要將範圍鎖住,改為如下
=IFERROR(INDEX(Sheet1!B:B,SMALL(IF((Sheet1!B$8:B$52<>"")*ISNUMBER(FIND("柜",Sheet1!B$8:C$52)),ROW($8:$52)),ROW(A1))),"")