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

422566739

hnpylzj 发表于 2025-3-30 09:33
非常感谢,冉请教下函数前面的@代表什么意思

溢出时数组的第一个元素

xtmyd5156

你的这个问题答案不唯一，只好给你拼起来了。

朝露孤烟

朝露孤烟 发表于 2025-3-30 12:01
=FILTER($F$2:$F$19,$G$2:$G$19=TAKE(TEXTSPLIT(B3,"区"),,-1))。

开始用FIND的结果做条件匹配，发现有重复返回值。于是改为TEXTSPLIT结果做条件，才能精确匹配，得到唯一值。

henterwu

用这个自定义ivlookup函数的x=2试试。
Function iVlookup(inText As String, inRng1 As Range, inRng2 As Range, x As Byte) As String
iVlookup = ""
arr1 = inRng1.Value
arr2 = inRng2.Value
If UBound(arr1) <> UBound(arr2) Or UBound(arr1, 2) <> UBound(arr2, 2) Then Exit Function

If x = 0 Then
  For i = 1 To UBound(arr1)
    If arr1(i, 1) = inText Then
      iVlookup = iVlookup & arr2(i, 1) & ","
    End If
  Next
  iVlookup = Left(iVlookup, Len(iVlookup) - 1)
End If

If x = 1 Then
  For i = 1 To UBound(arr1)
    If InStr(arr1(i, 1), inText) > 0 Then
      iVlookup = iVlookup & "{" & arr1(i, 1) & "," & arr2(i, 1) & "};"
    End If
  Next
  iVlookup = Left(iVlookup, Len(iVlookup) - 1)
End If

If x = 2 Then
  For i = 1 To UBound(arr1)
    If InStr(inText, arr1(i, 1)) > 0 Then
      iVlookup = iVlookup & "{" & arr1(i, 1) & "," & arr2(i, 1) & "};"
    End If
  Next
  iVlookup = Left(iVlookup, Len(iVlookup) - 1)
End If

End Function

waternew

=INDEX($F$2:$F$19,SMALL(IF(ISNUMBER(FIND($G$2:$G$19,B2)),ROW($1:$18),2^10),1))

wbjjzhu

WPS

1. =XLOOKUP(REGEXP(B2,"(?<=小区).+"),$G$2:$G$19,$F$2:$F$19,"",0)

复制代码