excel一对多查询新思路，自制多功能查询函数比vlookup函数更简单

在工作中我们经常会碰到根据某个单一条件去查找对应的数据值，这个时候我们常用的一个万能查询函数那就是vlookup函数，vlookup函数可以实现基本的向左、向右以及多条件值数据查询等功能。但是这个函数有个弊端就是，不能实现返回多个数据值。

如当我们在查询某个人当天所有门禁刷卡时间或当天人员的所有销售记录时候，从上往下查找只能查找出最上面的第一条数据，无法提取出整天的数据。如果要实现这个功能就需要用辅助操作来实现，会显得比较麻烦。那么今天我们就来讲讲自定义多功能查询函数和vlookup函数分别是如何解决这个问题的。

方法一、vlookup函数如何查找返回多个数据值

问题：提取张三7月1日所有刷卡记录

如上图效果图所示，当我们输入函数=VLOOKUP(ROW(A1),A:D,4,0)往下拖动，张三当天的所有刷卡记录都会显示出来，因为总共只有3条数据，所以第四条结果开始就会出现错误值。

操作方法：

第一步：首先用countif函数做一个辅助列，因为单纯的vlookup函数查询是无法返回多个数值的。插入A列，辅助列函数为：COUNTIF(C$2:C2,F$4)。

注意点：函数COUNTIF函数中C$2:C2，是非常有深意的，用相对引用的方式往下拖动，分别代表的数据区域则为：C$2:C3、C$2:C4、C$2:C5等。这样代表的意思就是可以查找出对应的人出现过多少次。

第二步：输入函数VLOOKUP(ROW(A1),A:D,4,0)进行数据查询，然后往下拖动即可返回姓名为张三的所有值。

注意点：vlookup函数第一参数使用ROW(A1)为条件值的目的是，通过对应姓名所在的数值来进行数据查询。比如第一条记录8:38分，选择函数ROW(A1)按F9，返回的是1；第二条记录10:15分，选择函数ROW(A1)按F9，返回的是2，以此类推。效果如下图所示：

方法二：自定义Mlookup多功能函数查找返回多个数据值

问题：提取张三7月1日所有销售单号

如上图效果图所示，输入函数：Nlookup(F4,C:D,2,-1)，即可返回张三7月1日销售的所有单号：2018070101,2018070106,2018070111，是不是感觉比vlookup函数更加简单神奇。这需要用到的是VBA代码来自定义一个Nlookup函数。

操作方法：

第一步：按alt+f11进入代码编辑窗口，新建一个模块；

第二步：输入以下代码后，保存为宏文件，即可使用自定义的Nlookup函数，如果你需要修改为其他自己喜欢的函数，可以全部替换即可。

代码如下：

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

Dim arr1, ARR2, 列数

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

列数 = 列数 + 1

End If

Next R

Else

cc = arr1

End If

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

For X = 1 To UBound(ARR2)

sr = ""

If 列数 > 1 Then

For q = 1 To 列数

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 列数 > 1 Then

For q = 1 To 列数

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 列数 > 1 Then

For q = 1 To 列数

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

学习完上面的两种查询多个数据的方法，你现在认为哪一种方法更加简单了？当然这个多功能函数还包含有其他的功能，赶快尝试一下吧。