用什么公式可以获取小区编号

mykitami

=ARRAYTOTEXT(FILTER($F$2:$F$19,ISNUMBER(FIND($G$2:$G$19,B2))))

mengrs

=LET(f,LAMBDA(a,SORTBY(a,LEN(G$2:G$19),-1)),@TOCOL(IF(ISNUMBER(FIND(f(G$2:G$19),B2)),f(F$2:F$19),x),3))

zhangcheng6688

借楼学习一下数组
Sub test()
arr = [a1].CurrentRegion
brr = [f1].CurrentRegion
For i = 2 To UBound(arr)
   For j = 2 To UBound(brr)
      If InStr(arr(i, 2), brr(j, 2)) Then
         arr(i, 3) = brr(j, 1)
         GoTo x
      End If
   Next
   arr(i, 3) = "未找到"
x:
Next
[a1].CurrentRegion = arr
End Sub

mengrs

小区编号和名称按字数升序排列才可以匹配到名称字数最多的编号
=LOOKUP(,0/FIND(SORTBY(G$2:G$19,LEN(G$2:G$19)),B2),SORTBY(F$2:F$19,LEN(G$2:G$19)))

快乐王子238

=MAP(TRIMRANGE(B2:B9999,2),LAMBDA(x,XLOOKUP(TRUE,REGEXP(x,G2:G19,1),F2:F19,"无此编号！")))

菜狗一只

1. =VLOOKUP(MID(B2,FIND("区",B2)+1,99),CHOOSECOLS($F$2:$G$19,2,1),2,FALSE)

复制代码

mengrs

=@TOCOL(IF(ISNUMBER(FIND(G$2:G$19,B2)),F$2:F$19,x),3)

朝露孤烟

=FILTER($F$2:$F$19,$G$2:$G$19=TAKE(TEXTSPLIT(B3,"区"),,-1))。

那天的月亮

=MAP(B3:B30,LAMBDA(x,@TOCOL(IF(REGEXP(x,$G$2:$G$19,1),F$2:F$19,z),3)))

wyslw2004

=FILTER($F$2:$F$19,ISNUMBER(FIND($G$2:$G$19,B3)))