本帖最后由 baksy 于 2014-5-13 16:27 编辑
公式长了点,凑合用吧~
sheet1B3
=INDEX(省市县列表!F:F,MATCH(MID(A3,IF(MIN(IF(MID(A3,ROW($1:$60),1)={"县","区","旗"},ROW($1:$60))),MAX(IF(MID(A3,ROW($1:$60),1)={"市","省"},ROW($1:$60))),FIND("省",A3))+1,MAX(IF(MID(A3,ROW($1:$60),1)={"县","市","区","旗"},ROW($1:$60)))-IF(MIN(IF(MID(A3,ROW($1:$60),1)={"县","区","旗"},ROW($1:$60))),MAX(IF(MID(A3,ROW($1:$60),1)={"市","省"},ROW($1:$60))),FIND("省",A3))),省市县列表!E:E,0))
公式复制后三键回车( 公式复制后,点一下公式编辑栏的任意位置,先按住 shift、ctrl 两个键,然后敲enter键),下拉。