从名字缩写找出相对应名字

sandays

=TOROW(FILTER(H$5:H$11,REGEXTEST(B5,G$5:G$11)))

niko88819

C16

1. =niko(B5:B11,H5:H12,G5:G12)

复制代码

1. Function niko(dataRange As Range, valueRange As Range, searchRange As Range) As Variant
   
2.     Dim result() As Variant
   
3.     Dim i As Long, j As Long, k As Long
   
4.     Dim splitValues As Variant
   
5.     Dim tempList As Object
   
6.     Dim dict As Object
   
7.     Dim maxColumns As Long
   
8.     Set dict = CreateObject("Scripting.Dictionary")
   
9.     Set tempList = CreateObject("System.Collections.ArrayList")
   
10.     For i = 1 To searchRange.Rows.Count
    
11.         dict(searchRange.Cells(i, 1).Value) = valueRange.Cells(i, 1).Value
    
12.     Next i
    
13.     maxColumns = 1
    
14.     Dim tempCounts() As Long
    
15.     ReDim tempCounts(1 To dataRange.Rows.Count)
    
16.     For i = 1 To dataRange.Rows.Count
    
17.         tempList.Clear
    
18.         splitValues = Split(dataRange.Cells(i, 1).Value, " ")
    
19.         For j = LBound(splitValues) To UBound(splitValues)
    
20.             If dict.exists(splitValues(j)) Then
    
21.                 tempList.Add dict(splitValues(j))
    
22.             End If
    
23.         Next j
    
24.         tempCounts(i) = tempList.Count
    
25.         If tempList.Count > maxColumns Then maxColumns = tempList.Count
    
26.     Next i
    
27.     ReDim result(1 To dataRange.Rows.Count, 1 To maxColumns)
    
28.     For i = 1 To UBound(result, 1)
    
29.         For j = 1 To UBound(result, 2)
    
30.             result(i, j) = ""
    
31.         Next j
    
32.     Next i
    
33.     For i = 1 To dataRange.Rows.Count
    
34.         tempList.Clear
    
35.         splitValues = Split(dataRange.Cells(i, 1).Value, " ")
    
36.         For j = LBound(splitValues) To UBound(splitValues)
    
37.             If dict.exists(splitValues(j)) Then
    
38.                 tempList.Add dict(splitValues(j))
    
39.             End If
    
40.         Next j
    
41.         For k = 0 To tempList.Count - 1
    
42.             result(i, k + 1) = tempList(k)
    
43.         Next k
    
44.     Next i
    
45.     niko = result
    
46. End Function

复制代码

niko88819

8跟12楼公式跟代码避免遇到张三，张三丰情况


举例
B11 = RFj to chase
G12 = RFj
H12 = Rick Flair Junior

wbjjzhu

1. =VLOOKUP(REGEXP(B13,"([A-Z]){2}"),$G$5:$H$11,2,FALSE)

复制代码

niko88819

wbjjzhu 发表于 2025-3-21 10:43

老师，请看13楼情况

wbjjzhu

niko88819 发表于 2025-3-21 10:53
老师，请看13楼情况

roserice

=TOROW(REGEXEXTRACT(IF(REGEXTEST(B5,$G$5:$G$11,1)*1,$H$5:$H$11,0),"[\D]+",1),3)

niko88819

roserice 发表于 2025-3-21 22:09
=TOROW(REGEXEXTRACT(IF(REGEXTEST(B5,$G$5:$G$11,1)*1,$H$5:$H$11,0),"[\D]+",1),3)

还是有问题呢?请看附件