根据IP查找部门

我走我流

我写了个超长的，，，583
=IFERROR(LOOKUP(9^9,0/(FIND(REPLACE(SUBSTITUTE(D2,".","@",3),FIND("@",SUBSTITUTE(D2,".","@",3)),99,),$A$2:$A$7)*(REPLACE(SUBSTITUTE(D2,".","@",3),1,FIND("@",SUBSTITUTE(D2,".","@",3)),)-REPLACE(REPLACE($A$2:$A$7,1,FIND("@",SUBSTITUTE($A$2:$A$7,".","@",3)),),FIND("-",REPLACE($A$2:$A$7,1,FIND("@",SUBSTITUTE($A$2:$A$7,".","@",3)),)),99,)>=0)*(REPLACE(SUBSTITUTE(D2,".","@",3),1,FIND("@",SUBSTITUTE(D2,".","@",3)),)-REPLACE(REPLACE($A$2:$A$7,1,FIND("@",SUBSTITUTE($A$2:$A$7,".","@",3)),),1,FIND("-",REPLACE($A$2:$A$7,1,FIND("@",SUBSTITUTE($A$2:$A$7,".","@",3)),)),)<=0)),$B$2:$B$7),"")

micch

kevinlee1109 发表于 2019-6-19 10:25
8楼公式不是已经可以用了吗？
而且我觉得这种问题，没必要追求单一公式，遇到问题解决问题便是，怎么方便 ...

这个是练习题，想来不是为了解决问题，而是为了练习函数

sayhi95

PQ大法好，哈哈~，处理参照表，再简单合并查询就出来了

solextrade

291字符  ：（

=IFERROR(INDEX(B:B,MATCH(1,MMULT(--(--MID(D2,FIND("@",SUBSTITUTE(D2,".","@",3))+1,9)>=--MID(SUBSTITUTE(MID($A$2:$A$7,FIND("@",SUBSTITUTE($A$2:$A$7,".","@",3))+1,9),"-",REPT(" ",9)),{1,9},9))*COUNTIF(OFFSET($A$1,ROW($1:$6),,),LEFT(D2,FIND("@",SUBSTITUTE(D2,".","@",3))-1)&"*"),{1;1}),)+1),"")

改到自己极限了，242字符：（（

=IFERROR(INDEX(B:B,MATCH(,0/((ABS(MMULT((SIGN(MID(D2,FIND("@",SUBSTITUTE(D2,".","@",3))+1,9)-MID(SUBSTITUTE(MID(A$2:A$7,FIND(".",A$2:A$7,10)+1,9),"-","    "),{1,6},5))),{1;1}))<2)*(COUNTIF(OFFSET($A$1,ROW($1:$6),,),LEFT(D2,10)&"*"))),)+1),"")

丢丢表格

micch 发表于 2019-6-19 10:54
这个是练习题，想来不是为了解决问题，而是为了练习函数

给个 火车  就是计算量有点多

=INDEX(B:B,TEXT(SUM(IFNA(MATCH(SUBSTITUTE(D2&999,MAX(RIGHT(D2&999,{4,5,6})*1),)&{"*-",""}&MAX(RIGHT(D2&0,{2,3,4})/10)+ROW($1:$254)-{1,0}&{"","-*"},A:A,)*{1,-1},)),"0;;99"))&""

1. =INDEX(B:B,TEXT(SUM(IFNA(MATCH(LEFT(D2,COUNT(FIND(".",D2,ROW($1:$15))))&{"*-",""}&MAX(RIGHT(D2&0,{2,3,4})/10)+ROW($1:$254)-{1,0}&{"","-*"},A:A,)*{1,-1},)),"0;;99"))&""

复制代码

江苏永能朱兴华

丢丢表格 发表于 2019-6-19 14:07
给个 169 的，就是计算量有点多

=INDEX(B:B,TEXT(SUM(IFNA(MATCH(SUBSTITUTE(D2&999,MAX(RIGHT(D2&999 ...

跟丢丢老大学习下：
=INDEX(B:B,TEXT(SUM(IFERROR(MATCH(LEFT(D2,LEN(D2)-COUNT(-(1%&RIGHT(D2,{1,2,3}))))&{"*-",""}&MAX(RIGHT(D2&0,{2,3,4})/10)+ROW($1:$254)-{1,0}&{"","-*"},A:A,)*{1,-1},)),"0;;99"))&""

sucx007

我走我流 发表于 2019-6-19 10:40
我写了个超长的，，，583
=IFERROR(LOOKUP(9^9,0/(FIND(REPLACE(SUBSTITUTE(D2,".","@",3),FIND("@",SUBST ...

没分送了，下次补上

sucx007

丢丢表格 发表于 2019-6-19 14:07
给个 169 的，就是计算量有点多

=INDEX(B:B,TEXT(SUM(IFNA(MATCH(SUBSTITUTE(D2&999,MAX(RIGHT(D2&999 ...

192.168.5.81，丢丢老师，这个好像有问题

丢丢表格

sucx007 发表于 2019-6-19 15:22
192.168.5.81，丢丢老师，这个好像有问题

是有误，谢诣正、现修正：
=INDEX(B:B,TEXT(SUM(IFNA(MATCH(LEFT(D2,COUNT(FIND(".",D2,ROW($1:$15))))&{"*-",""}&MAX(RIGHT(D2&0,{2,3,4})/10)+ROW($1:$254)-{1,0}&{"","-*"},A:A,)*{1,-1},)),"0;;99"))&""

micch

一直在想怎么用match模糊查找，睡个午觉功夫丢丢老师就给解决了，255个数据match没想到