IF嵌套超过了64层怎么办，有没有跟好的函数啊

gaowen1

标识.rar (11.15 KB, 下载次数: 52)

2019-10-22 10:21 上传

点击文件名下载附件

在A列中筛选包含F列的，在B列
显示出对应的G列名称


我在B2用了嵌套，但是超过了64层，无法计算了，有什么好的
其他函数公式吗？
下面这公式

1. IF(COUNTIF(A2,"*"&"HU3C3-"&"*")=1,"栾城县",IF(COUNTIF(A2,"*"&"Y3C1-"&"*")=1,"行唐县",IF(COUNTIF(A2,"*"&"7A1"&"*")=1,"灵寿县",IF(COUNTIF(A2,"*"&"XHA1-"&"*")=1,"高邑县",IF(COUNTIF(A2,"*"&"XR4A1-"&"*")=1,"沈泽县",IF(COUNTIF(A2,"*"&"XA1-"&"*")=1,"赞皇县",IF(COUNTIF(A2,"*"&"RA1-"&"*")=1,"无极县",IF(COUNTIF(A2,"*"&"J2T4-"&"*")=1,"平山县",IF(COUNTIF(A2,"*"&"NND23-"&"*")=1,"元氏县",IF(COUNTIF(A2,"*"&"YYD23-"&"*")=1,"赵县",,IF(COUNTIF(A2,"*"&"M22A1-"&"*")=1,"辛集市",,IF(COUNTIF(A2,"*"&"22MA1-"&"*")=1,"藁城市",,IF(COUNTIF(A2,"*"&"AMD03-"&"*")=1,"武安市",,IF(COUNTIF(A2,"*"&"JT5-"&"*")=1,"邢台市",,IF(COUNTIF(A2,"*"&"GF1-"&"*")=1,"市辖区",,IF(COUNTIF(A2,"*"&"DHC04-"&"*")=1,"桥东区",,IF(COUNTIF(A2,"*"&"DHC02-"&"*")=1,"桥西区",,IF(COUNTIF(A2,"*"&"DHC01-"&"*")=1,"邢台县",,IF(COUNTIF(A2,"*"&"BNS1-"&"*")=1,"临城县",,IF(COUNTIF(A2,"*"&"BNWERS1-"&"*")=1,"内丘县",,IF(COUNTIF(A2,"*"&"GH1-"&"*")=1,"柏乡县",,IF(COUNTIF(A2,"*"&"BNJGS1D9-"&"*")=1,"隆尧县",,IF(COUNTIF(A2,"*"&"GFDS1-"&"*")=1,"任县",,IF(COUNTIF(A2,"*"&"AMD12-"&"*")=1,"巨鹿县",,IF(COUNTIF(A2,"*"&"HVD12-"&"*")=1,"南和县",,IF(COUNTIF(A2,"*"&"XBA1-"&"*")=1,"",,IF(COUNTIF(A2,"*"&"XFWD03-"&"*")=1,"广宗县",,IF(COUNTIF(A2,"*"&"CVW1-"&"*")=1,"",,IF(COUNTIF(A2,"*"&"BNBDS1-"&"*")=1,"平乡县",,IF(COUNTIF(A2,"*"&"55ES1-"&"*")=1,"保定市",,IF(COUNTIF(A2,"*"&"JMS1-"&"*")=1,"",,IF(COUNTIF(A2,"*"&"HJHS1-"&"*")=1,"南宫市",,IF(COUNTIF(A2,"*"&"AMD9-"&"*")=1,"清河县",,IF(COUNTIF(A2,"*"&"AMD6-"&"*")=1,"沙河市",,IF(COUNTIF(A2,"*"&"AMD1-"&"*")=1,"威县",,IF(COUNTIF(A2,"*"&"TA1-"&"*")=1,"新市区",,IF(COUNTIF(A2,"*"&"JT9-"&"*")=1,"市辖区",,IF(COUNTIF(A2,"*"&"JT2-"&"*")=1,"北市区",,IF(COUNTIF(A2,"*"&"VBS4-"&"*")=1,"涞水县",,IF(COUNTIF(A2,"*"&"GF1-"&"*")=1,"清苑县",,IF(COUNTIF(A2,"*"&"TR34-"&"*")=1,"南市区",IF(COUNTIF(A2,"*"&"ZSW2-"&"*")=1,"阜平县",IF(COUNTIF(A2,"*"&"SZYZ1-"&"*")=1,"徐水县",IF(COUNTIF(A2,"*"&"SZYZ2-"&"*")=1,"定兴县",IF(COUNTIF(A2,"*"&"DHC03-"&"*")=1,"福山区",IF(COUNTIF(A2,"*"&"AMD23-"&"*")=1,"",IF(COUNTIF(A2,"*"&"ZFTC1-"&"*")=1,"唐县",IF(COUNTIF(A2,"*"&"AMD14-"&"*")=1,"莱山区",IF(COUNTIF(A2,"*"&"NJ1"&"*")=1,"长岛县",IF(COUNTIF(A2,"*"&"ZXXF1-"&"*")=1,"龙口市",IF(COUNTIF(A2,"*"&"ZXXF2-"&"*")=1,"莱州市",IF(COUNTIF(A2,"*"&"ZXXF3-"&"*")=1,"莱阳市",IF(COUNTIF(A2,"*"&"HUC1-"&"*")=1,"招远市",IF(COUNTIF(A2,"*"&"HUC2-"&"*")=1,"海阳市",IF(COUNTIF(A2,"*"&"HUC2-"&"*")=1,"栖霞市",IF(COUNTIF(A2,"*"&"HUC3-"&"*")=1,"蓬莱市",IF(COUNTIF(A2,"*"&"YC1-"&"*")=1,"市辖区",IF(COUNTIF(A2,"*"&"HANC1-"&"*")=1,"潍坊市",IF(COUNTIF(A2,"*"&"FX1-"&"*")=1,"潍城区",IF(COUNTIF(A2,"*"&"GJFX1-"&"*")=1,"寒亭区",IF(COUNTIF(A2,"*"&"GJLX1-"&"*")=1,"坊子区",IF(COUNTIF(A2,"*"&"LX1-"&"*")=1,"奎文区",IF(COUNTIF(A2,"*"&"XLLC1-"&"*")=1,"临朐",IF(COUNTIF(A2,"*"&"ZTZFFX1-"&"*")=1,"昌乐县",IF(COUNTIF(A2,"*"&"BDXXL-"&"*"&"AMD4-"&"*")=1,"青州市",IF(COUNTIF(A2,"*"&"BDXXL-"&"*"&"AMD2-"&"*")=1,"诸城市",IF(COUNTIF(A2,"*"&"BDXXL-"&"*"&"AMA2-"&"*")=1,"安丘市",IF(COUNTIF(A2,"*"&"BDXXL-"&"*"&"JT1-"&"*")=1,"寿光市",IF(COUNTIF(A2,"*"&"BDXXL-"&"*"&"LB1-"&"*")=1,"高密市",IF(COUNTIF(A2,"*"&"BDXXL-"&"*"&"AMD1-"&"*")=1,"昌邑市","无轨迹")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

复制代码

gaowen1

1. IF(COUNTIF(A2,"*"&"HU3C3-"&"*")=1,"栾城县",IF(COUNTIF(A2,"*"&"Y3C1-"&"*")=1,"行唐县",IF(COUNTIF(A2,"*"&"7A1"&"*")=1,"灵寿县",IF(COUNTIF(A2,"*"&"XHA1-"&"*")=1,"高邑县",IF(COUNTIF(A2,"*"&"XR4A1-"&"*")=1,"沈泽县",IF(COUNTIF(A2,"*"&"XA1-"&"*")=1,"赞皇县",IF(COUNTIF(A2,"*"&"RA1-"&"*")=1,"无极县",IF(COUNTIF(A2,"*"&"J2T4-"&"*")=1,"平山县",IF(COUNTIF(A2,"*"&"NND23-"&"*")=1,"元氏县",IF(COUNTIF(A2,"*"&"YYD23-"&"*")=1,"赵县",,IF(COUNTIF(A2,"*"&"M22A1-"&"*")=1,"辛集市",,IF(COUNTIF(A2,"*"&"22MA1-"&"*")=1,"藁城市",,IF(COUNTIF(A2,"*"&"AMD03-"&"*")=1,"武安市",,IF(COUNTIF(A2,"*"&"JT5-"&"*")=1,"邢台市",,IF(COUNTIF(A2,"*"&"GF1-"&"*")=1,"市辖区",,IF(COUNTIF(A2,"*"&"DHC04-"&"*")=1,"桥东区",,IF(COUNTIF(A2,"*"&"DHC02-"&"*")=1,"桥西区",,IF(COUNTIF(A2,"*"&"DHC01-"&"*")=1,"邢台县",,IF(COUNTIF(A2,"*"&"BNS1-"&"*")=1,"临城县",,IF(COUNTIF(A2,"*"&"BNWERS1-"&"*")=1,"内丘县",,IF(COUNTIF(A2,"*"&"GH1-"&"*")=1,"柏乡县",,IF(COUNTIF(A2,"*"&"BNJGS1D9-"&"*")=1,"隆尧县",,IF(COUNTIF(A2,"*"&"GFDS1-"&"*")=1,"任县",,IF(COUNTIF(A2,"*"&"AMD12-"&"*")=1,"巨鹿县",,IF(COUNTIF(A2,"*"&"HVD12-"&"*")=1,"南和县",,IF(COUNTIF(A2,"*"&"XBA1-"&"*")=1,"",,IF(COUNTIF(A2,"*"&"XFWD03-"&"*")=1,"广宗县",,IF(COUNTIF(A2,"*"&"CVW1-"&"*")=1,"",,IF(COUNTIF(A2,"*"&"BNBDS1-"&"*")=1,"平乡县",,IF(COUNTIF(A2,"*"&"55ES1-"&"*")=1,"保定市",,IF(COUNTIF(A2,"*"&"JMS1-"&"*")=1,"",,IF(COUNTIF(A2,"*"&"HJHS1-"&"*")=1,"南宫市",,IF(COUNTIF(A2,"*"&"AMD9-"&"*")=1,"清河县",,IF(COUNTIF(A2,"*"&"AMD6-"&"*")=1,"沙河市",,IF(COUNTIF(A2,"*"&"AMD1-"&"*")=1,"威县",,IF(COUNTIF(A2,"*"&"TA1-"&"*")=1,"新市区",,IF(COUNTIF(A2,"*"&"JT9-"&"*")=1,"市辖区",,IF(COUNTIF(A2,"*"&"JT2-"&"*")=1,"北市区",,IF(COUNTIF(A2,"*"&"VBS4-"&"*")=1,"涞水县",,IF(COUNTIF(A2,"*"&"GF1-"&"*")=1,"清苑县",,IF(COUNTIF(A2,"*"&"TR34-"&"*")=1,"南市区",IF(COUNTIF(A2,"*"&"ZSW2-"&"*")=1,"阜平县",IF(COUNTIF(A2,"*"&"SZYZ1-"&"*")=1,"徐水县",IF(COUNTIF(A2,"*"&"SZYZ2-"&"*")=1,"定兴县",IF(COUNTIF(A2,"*"&"DHC03-"&"*")=1,"福山区",IF(COUNTIF(A2,"*"&"AMD23-"&"*")=1,"",IF(COUNTIF(A2,"*"&"ZFTC1-"&"*")=1,"唐县",IF(COUNTIF(A2,"*"&"AMD14-"&"*")=1,"莱山区",IF(COUNTIF(A2,"*"&"NJ1"&"*")=1,"长岛县",IF(COUNTIF(A2,"*"&"ZXXF1-"&"*")=1,"龙口市",IF(COUNTIF(A2,"*"&"ZXXF2-"&"*")=1,"莱州市",IF(COUNTIF(A2,"*"&"ZXXF3-"&"*")=1,"莱阳市",IF(COUNTIF(A2,"*"&"HUC1-"&"*")=1,"招远市",IF(COUNTIF(A2,"*"&"HUC2-"&"*")=1,"海阳市",IF(COUNTIF(A2,"*"&"HUC2-"&"*")=1,"栖霞市",IF(COUNTIF(A2,"*"&"HUC3-"&"*")=1,"蓬莱市",IF(COUNTIF(A2,"*"&"YC1-"&"*")=1,"市辖区",IF(COUNTIF(A2,"*"&"HANC1-"&"*")=1,"潍坊市",IF(COUNTIF(A2,"*"&"FX1-"&"*")=1,"潍城区",IF(COUNTIF(A2,"*"&"GJFX1-"&"*")=1,"寒亭区",IF(COUNTIF(A2,"*"&"GJLX1-"&"*")=1,"坊子区",IF(COUNTIF(A2,"*"&"LX1-"&"*")=1,"奎文区",IF(COUNTIF(A2,"*"&"XLLC1-"&"*")=1,"临朐",IF(COUNTIF(A2,"*"&"ZTZFFX1-"&"*")=1,"昌乐县",IF(COUNTIF(A2,"*"&"BDXXL-"&"*"&"AMD4-"&"*")=1,"青州市",IF(COUNTIF(A2,"*"&"BDXXL-"&"*"&"AMD2-"&"*")=1,"诸城市",IF(COUNTIF(A2,"*"&"BDXXL-"&"*"&"AMA2-"&"*")=1,"安丘市",IF(COUNTIF(A2,"*"&"BDXXL-"&"*"&"JT1-"&"*")=1,"寿光市",IF(COUNTIF(A2,"*"&"BDXXL-"&"*"&"LB1-"&"*")=1,"高密市",IF(COUNTIF(A2,"*"&"BDXXL-"&"*"&"AMD1-"&"*")=1,"昌邑市","无轨迹")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

复制代码

静看流年RSSQ

我一直很佩服这种很长的IF  还不错的。

一般我都向着自己熟悉的方案走。

火车，针对附件。仅供参考、

=VLOOKUP(TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("-",A2,FIND("-",A2)+1)+1,99),"-",REPT(" ",99)),99)),F:G,2,)

micch

写这么多常量，何不做个照表，vlookup直接取值？？

piny

micch 发表于 2019-10-22 11:34
写这么多常量，何不做个照表，vlookup直接取值？？

有呀, 有做對照表 ><

满坛皆为吾师

1. =LOOKUP(,-FIND(F$2:F$73,A2),G$2:G$73)

复制代码

micch

piny 发表于 2019-10-22 11:39
有呀, 有做對照表 >

啊哈，没看附件，-_-||



用版主大人的lookU+find正解
=index(g:g,max(row($2:$73)*countif(a2,"*"f$2:f$73&"*")))

• =LOOKUP(,-FIND(F$2:F$73,A2),G$2:G$73)
  

tiamozy125

上面各种大神的方法都是神来之笔，学习了

自己也尝试了下，楼主也可以参考一下。

1. =IFERROR(VLOOKUP(MID(A2,FIND("q-",A2)+2,FIND("-",MID(A2,FIND("q-",A2)+2,66))-1),F:G,2,0),"")

复制代码

libo5563

pq 处理的试试

strong20

=LOOKUP(1,-FIND($F$2:$F$73,A2),($G$2:$G$73))别处借鉴的公式