眾所周知，在使用vlookup查找數(shù)據(jù)時(shí)笨腥，只能返回一個(gè)符合條件的值刨摩。那么如何實(shí)現(xiàn)返回所有符合條件的值呢？

舉例

如果要在B13返回條件為 “性別男” 的所有姓名俭厚，B14返回條件為 “性別女” 的所有姓名户魏，如何使用公式呢？以下有2種VBA自定義函數(shù)可實(shí)現(xiàn)

以下2種VBA代碼都摘自網(wǎng)絡(luò)挪挤，僅對其細(xì)微修改

1叼丑，簡易的，只能返回單條件且相等的值

VBA自定義函數(shù)代碼

Function Contxt_1(a As Range, b As Range, c As String)
    
    '函數(shù)定義Contxt_1(條件區(qū)域扛门，返回值區(qū)域鸠信，條件)，簡易版本
    '自定義函數(shù)返回符合匹配條件的所有值论寨，以"星立，"分割爽茴，但結(jié)果會以"，"開頭绰垂，可用函數(shù)=MID(i,2,LEN(i)-1)去除
    '要求：a和b2個(gè)區(qū)域不能為整列室奏，需為有大小的區(qū)域，且大小相同劲装，為整列時(shí)數(shù)據(jù)計(jì)算量巨大可能導(dǎo)致程序崩潰
    Dim t As String
    '如果a與b的區(qū)域大小不同胧沫，就顯示“錯(cuò)誤”
    If a.Rows.Count <> b.Rows.Count Then Contxt_1 = "錯(cuò)誤": Exit Function
    '在區(qū)域a循環(huán)
    For i = 1 To a.Rows.Count
        '如果在a中找到與c相同的值，就把同一行中的b的內(nèi)容提取出來酱畅，存入變量t中琳袄，同時(shí)插入分隔符"江场，"纺酸。
        If a.Cells(i, 1) = c Then t = t & "，" & b.Cells(i, 1)
    Next
    '將變量的值賦給自定義函數(shù)址否，同時(shí)去除開頭的"餐蔬，"
    t = Mid(t, 2, Len(t) - 1)
    Contxt_1 = t

End Function

在B13處輸入公式

=Contxt_1($B$2:$B$9,$A$2:$A$9,A13)

然后回車即可得到結(jié)果，B13下拉公式至B14佑附，如圖：

公式

結(jié)果

公式中“VBA宏文件.xlsm”為WPS保存VBA代碼的文件

2樊诺，復(fù)雜的，能返回符合多條件的值

VBA自定義函數(shù)代碼

Function contxt(ParamArray args() As Variant) As Variant
    '函數(shù)定義Contxt(返回值區(qū)域&分隔符),返回值有為空的也會返回音同，沒有分隔符則直接拼接字符串词爬，如Contxt(1,2,3)返回12
    '在WPS中，單元格區(qū)域在256個(gè)以內(nèi)才行权均，超過了就不能返回?cái)?shù)組顿膨，Office的Excel無此限制
    Dim tmptext As Variant, i As Variant, Cellv As Variant
    Dim Cell As Range
    tmptext = ""
    
    For i = 0 To UBound(args)
        If Not IsMissing(args(i)) Then
            Select Case TypeName(args(i))
                Case "Range"
                    For Each Cell In args(i)
                        tmptext = tmptext & Cell
                    Next Cell
                Case "Variant()"
                    For Each Cellv In args(i)
                        tmptext = tmptext & Cellv
                    Next Cellv
                Case Else
                    tmptext = tmptext & args(i)
            End Select
        End If
    Next i
    '將變量的值賦給自定義函數(shù)，同時(shí)去除末尾的分隔符
    tmptext = Mid(tmptext, 1, Len(tmptext) - 1)
    contxt = tmptext
    
End Function

值得注意的是叽赊，“在WPS中恋沃，單元格區(qū)域在256個(gè)以內(nèi)才行，超過了就不能返回?cái)?shù)組”必指，即下行公式單元格區(qū)域B2:B9共8個(gè)單元格囊咏，小于256，因此在WPS可以得到結(jié)果塔橡。經(jīng)過測試在Office的Excel無此限制

在B13處輸入公式

=Contxt(IF(($B$2:$B$9=A13),$A$2:$A$9&"梅割，",""))

然后按鍵盤 ctrl+shift+enter 得到數(shù)組公式，B13下拉公式至B14葛家，如圖：

公式

結(jié)果

2個(gè)自定義函數(shù)返回的結(jié)果是一致的

2.1户辞，contxt函數(shù)最簡單的運(yùn)用

假設(shè)，單元格A1=1惦银，A2=2咆课，A3=3
在A4單元格使用數(shù)組公式{=Contxt(A1:A3&"末誓，")}，返回值如下

contxt返回值

2.2书蚪，contxt函數(shù)返回符合多條件的值

如果要在B13返回條件為 “性別男喇澡，分?jǐn)?shù)低于60” 的所有姓名，B14返回條件為 “性別女殊校，分?jǐn)?shù)低于60” 的所有姓名晴玖，如何使用公式呢？
在B13處輸入公式

=Contxt((IF(($B$2:$B$9=A13)*($C$2:$C$9<60),$A$2:$A$9&"为流，","")))

然后按鍵盤 ctrl+shift+enter 得到數(shù)組公式呕屎，B13下拉公式至B14，如圖：

公式

結(jié)果

3敬察，使用方法類似vlookup返回所有符合條件的值

Function VLOOKUP_ALL(lookup_value As String, table_array As Range, Optional col_index As Integer = 2) As String
    '函數(shù)定義VLOOKUP_ALL(要查找的值秀睛，查找區(qū)域，匹配值所在列數(shù))返回與要查找的值匹配的所有結(jié)果
    Dim arr, i As Long, delimiter As String, result As String
    arr = table_array.Value
    delimiter = "莲祸，"   '分隔符
    For i = 1 To UBound(arr)
        If arr(i, 1) = lookup_value Then
            result = result & delimiter & arr(i, col_index)
        End If
    Next
    Select Case result
        Case ""
            VLOOKUP_ALL = "#N/A#"  '未查找返回值蹂安，區(qū)分函數(shù)未正確運(yùn)行"#N/A"
        Case Else
            VLOOKUP_ALL = Right(result, Len(result) - Len(delimiter))  '返回結(jié)果，同時(shí)去除開頭的分隔符
    End Select
    
End Function

Sub VLOOKUP_ALL幫助信息()
    '運(yùn)行一次后該幫助信息生效
    Dim 函數(shù)名稱 As String        '函數(shù)名稱
    Dim 函數(shù)描述 As String        '函數(shù)描述
    Dim 參數(shù)(0 To 2) As String     '函數(shù)參數(shù)描述 數(shù)組 個(gè)數(shù)
    
    函數(shù)名稱 = "VLOOKUP_ALL"
    函數(shù)描述 = "擴(kuò)展VLOOKUP锐帜，可以返回所有匹配的值并用“田盈，”分隔，完全匹配"
    參數(shù)(0) = "要查找的值缴阎，單元格允瞧、文本字符串"
    參數(shù)(1) = "查找區(qū)域，同VLOOKUP蛮拔，第1列包含要查找的值"
    參數(shù)(2) = "匹配值所在列數(shù)述暂，同VLOOKUP，數(shù)字"
    
    Call Application.MacroOptions(macro:=函數(shù)名稱, Description:=函數(shù)描述, ArgumentDescriptions:=參數(shù))
    
End Sub

VLOOKUP_ALL()函數(shù)的另一種寫法

Function VLOOKUP_ALL(lookup_value As String, table_array As Range, Optional col_index As Integer = 2) As String
    '函數(shù)定義VLOOKUP_ALL(要查找的值语泽，查找區(qū)域贸典，匹配值所在列數(shù))返回與要查找的值匹配的所有結(jié)果
    Dim arr, i As Long, delimiter As String, srr, n
    arr = table_array.Value
    delimiter = "，"   '分隔符
    srr = Array()  '保存匹配的值踱卵，空數(shù)組
    For i = 1 To UBound(arr)
        If arr(i, 1) = lookup_value Then
            n = UBound(srr) + 1
            ReDim Preserve srr(n)  '重定義數(shù)組長度廊驼，但數(shù)據(jù)保留
            srr(n) = arr(i, col_index)
        End If
    Next
    VLOOKUP_ALL = Join(srr, delimiter)  '未查找返回值，返回空值
    
End Function