excel全称匹配简称，部分简称是包含关系

熊猫蓝天

excel全称匹配简称，部分简称是包含关系,求函数公式

nb446835

看不懂。。。

APPLE123

楼主的表格好像是wps的吧

象山海鲜

=lookup(1,0/countif(d2,a$2:a$6&"*"),b$2:b6)

jsgj2023

楼主不上附件,也不及时反馈,一点都不认真!

看见星光

http://club.excelhome.net/thread-1285206-1-1.html

coverne

不上附件真不是好习惯

coverne

=lookup(1,0/countif(d2,a$2:a$6&"*"),b$2:b6)

haps_star

自定义函数
Function MLookUp(Str As Range, Mkey As Range, Mvalue As Range)
    Dim Dic As Object
        Set Dic = CreateObject("Scripting.Dictionary")
        Dic.comparemode = 1
    Dim Regx
        Set Regx = CreateObject("vbscript.regexp")
   
    Dim Arr1(), Arr2(), Arr3()
    Arr1 = Mkey
    Arr2 = Mvalue
    Dim i

    For i = 1 To UBound(Arr1, 1)
     Dic(Arr1(i, 1)) = Arr2(i, 1)
     Next
    Dim Str1, Str2, Str3
    Arr3 = Dic.keys
    Str1 = Str: Str3 = ""
    For i = 0 To UBound(Arr3)
     Str2 = Arr3(i)
     With Regx
        .Global = True
        .Pattern = Str2
           If .test(Str1) = True Then
            Str3 = Dic(Str2)
           GoTo 100
          End If
      End With
      Next
100
    Set Regx = Nothing
    Set Dic = Nothing
    MLookUp = Str3
End Function

haps_star

自定义函数
Function MLookUp(Str As Range, Mkey As Range, Mvalue As Range)
    Dim Dic As Object
        Set Dic = CreateObject("Scripting.Dictionary")
        Dic.comparemode = 1
    Dim Regx
        Set Regx = CreateObject("vbscript.regexp")
   
    Dim Arr1(), Arr2(), Arr3()
    Arr1 = Mkey
    Arr2 = Mvalue
    Dim i

    For i = 1 To UBound(Arr1, 1)
     Dic(Arr1(i, 1)) = Arr2(i, 1)
     Next
    Dim Str1, Str2, Str3
    Arr3 = Dic.keys
    Str1 = Str: Str3 = ""
    For i = 0 To UBound(Arr3)
     Str2 = Arr3(i)
     With Regx
        .Global = True
        .Pattern = Str2
           If .test(Str1) = True Then
            Str3 = Dic(Str2)
           GoTo 100
          End If
      End With
      Next
100
    Set Regx = Nothing
    Set Dic = Nothing
    MLookUp = Str3
End Function