在工作中我們經(jīng)常會碰到根據(jù)某個單一條件去查找對應(yīng)的數(shù)據(jù)值，這個時候我們常用的一個萬能查詢函數(shù)那就是vlookup函數(shù)，vlookup函數(shù)可以實現(xiàn)基本的向左、向右以及多條件值數(shù)據(jù)查詢等功能。但是這個函數(shù)有個弊端就是，不能實現(xiàn)返回多個數(shù)據(jù)值。

如當(dāng)我們在查詢某個人當(dāng)天所有門禁刷卡時間或當(dāng)天人員的所有銷售記錄時候，從上往下查找只能查找出最上面的第一條數(shù)據(jù)，無法提取出整天的數(shù)據(jù)。如果要實現(xiàn)這個功能就需要用輔助操作來實現(xiàn)，會顯得比較麻煩。那么今天我們就來講講自定義多功能查詢函數(shù)和vlookup函數(shù)分別是如何解決這個問題的。

方法一、vlookup函數(shù)如何查找返回多個數(shù)據(jù)值

問題：提取張三7月1日所有刷卡記錄

如上圖效果圖所示，當(dāng)我們輸入函數(shù)=VLOOKUP(ROW(A1),A:D,4,0)往下拖動，張三當(dāng)天的所有刷卡記錄都會顯示出來，因為總共只有3條數(shù)據(jù)，所以第四條結(jié)果開始就會出現(xiàn)錯誤值。

操作方法：

第一步：首先用countif函數(shù)做一個輔助列，因為單純的vlookup函數(shù)查詢是無法返回多個數(shù)值的。插入A列，輔助列函數(shù)為：COUNTIF(C$2:C2,F$4)。

注意點：函數(shù)COUNTIF函數(shù)中C$2:C2，是非常有深意的，用相對引用的方式往下拖動，分別代表的數(shù)據(jù)區(qū)域則為：C$2:C3、C$2:C4、C$2:C5等。這樣代表的意思就是可以查找出對應(yīng)的人出現(xiàn)過多少次。

第二步：輸入函數(shù)VLOOKUP(ROW(A1),A:D,4,0)進行數(shù)據(jù)查詢，然后往下拖動即可返回姓名為張三的所有值。

注意點：vlookup函數(shù)第一參數(shù)使用ROW(A1)為條件值的目的是，通過對應(yīng)姓名所在的數(shù)值來進行數(shù)據(jù)查詢。比如第一條記錄8:38分，選擇函數(shù)ROW(A1)按F9，返回的是1；第二條記錄10:15分，選擇函數(shù)ROW(A1)按F9，返回的是2，以此類推。效果如下圖所示：

方法二：自定義Mlookup多功能函數(shù)查找返回多個數(shù)據(jù)值

問題：提取張三7月1日所有銷售單號

如上圖效果圖所示，輸入函數(shù)：Nlookup(F4,C:D,2,-1)，即可返回張三7月1日銷售的所有單號：2018070101,2018070106,2018070111，是不是感覺比vlookup函數(shù)更加簡單神奇。這需要用到的是VBA代碼來自定義一個Nlookup函數(shù)。

操作方法：

第一步：按alt+f11進入代碼編輯窗口，新建一個模塊；

第二步：輸入以下代碼后，保存為宏文件，即可使用自定義的Nlookup函數(shù)，如果你需要修改為其他自己喜歡的函數(shù)，可以全部替換即可。

代碼如下：

Function Nlookup(rg, rgs As Range, L As Integer, M As Integer)

Dim arr1, ARR2, 列數(shù)

Dim R, n, K, X, cc, sr As String

arr1 = rg.Value

ARR2 = rgs

If VBA.IsArray(arr1) Then

For Each R In arr1

If R <> "" Then

cc = cc & R

列數(shù) = 列數(shù) + 1

End If

Next R

Else

cc = arr1

End If

If M > 0 Then '非查找最后一個

For X = 1 To UBound(ARR2)

sr = ""

If 列數(shù) > 1 Then

For q = 1 To 列數(shù)

sr = sr & ARR2(X, q)

Next q

Else

sr = ARR2(X, 1)

End If

If sr = cc Then

K = K + 1

If K = M Then

Nlookup = ARR2(X, L)

Exit Function

End If

End If

Next X

ElseIf M = -1 Then '查找所有值

For X = 1 To UBound(ARR2)

sr = ""

If 列數(shù) > 1 Then

For q = 1 To 列數(shù)

sr = sr & ARR2(X, q)

Next q

Else

sr = ARR2(X, 1)

End If

If sr = cc Then

Nlookup = Nlookup & "," & ARR2(X, L)

End If

Next X

Nlookup = Right(Nlookup, Len(Nlookup) - 1)

Exit Function

Else '查找最后一個

For X = UBound(ARR2) To 1 Step -1

sr = ""

If 列數(shù) > 1 Then

For q = 1 To 列數(shù)

sr = sr & ARR2(X, q)

Next q

Else

sr = ARR2(X, 1)

End If

If sr = cc Then

Nlookup = ARR2(X, L)

Exit Function

End If

Next X

End If

Nlookup = ""

End Function

學(xué)習(xí)完上面的兩種查詢多個數(shù)據(jù)的方法，你現(xiàn)在認為哪一種方法更加簡單了？當(dāng)然這個多功能函數(shù)還包含有其他的功能，趕快嘗試一下吧。