Excel函数NLOOKUP查找并返回第N个匹配值

简介

      通常VLOOKUP函数只能返回查找到的第一个值，但有时我们需要返回查找到的第N个值，VLOOKUP函数就显得无能为力了，也有高手通过嵌套多层复杂函数来实现，但都不如直接利用NLOOKUP函数来得简单，而且它支持任意长度的字符串作为搜索目标和搜索范围，而VLOOKUP只支持255个字符。NLOOKUP的使用方法几乎跟VLOOKUP一样，仅仅是将最后一个参数改为返回索引序号就可以轻松实现。

使用方法

      例如，在表2的A列搜索表1的A1单元格，并返回表2中D列对应的第二个值，公式如下：

      NLOOKUP(Sheet1!A1,Sheet2!A:D,4,2)

原理

       NLOOK函数其实是一个自定义函数，也就是说Excel允许用户建立自己的函数，在使用体验上跟系统自带的函数是一样的。

创建方法

       创建自定义函数的方法就是在Excel电子表格的VBA程序模块中加入自建函数的代码，具体操作是依次点击“开发工具”—“Visual Basic”，如下图。

 

图1

 

图2

      在VBAProject工程的“模块”中粘贴并保存如下代码：

Option Explicit
Function NLOOKUP(查找值 As String, 区域 As Range, Optional 列 As Integer = 2, Optional 索引号 As Integer = 1) As String
'NLOOKUP是VLOOKUP的增强函数，可指定返回第N个值，且支持任意长度的字符串作为搜索目标或搜索范围。
'使用说明：NLOOKUP函数有4个参数，前两个为必选参数，后两个为可选参数
'第1参数代表查找对象；
'第2参数代表被查找区域，通常为多列的单元格区域
'第3参数代表返回值所在的列数，例如默认为2列；
'第4参数代表返回第几个值，默认为第一个

    Application.Volatile  '声明为易失性函数
    Dim i As Long, Cell As Range, Str As String
    With 区域.Columns(1) '引用区域的第一列
        '如果引用区域第一个单元格等于查找的对象，那么将该单元格赋予变量cell;否则使用find方法查找，将找到的单元格赋予变量cell
        If .Cells(1) = 查找值 Then Set Cell = .Cells(1) Else Set Cell = .Find(查找值, LookIn:=xlValues, lookat:=xlWhole)
        If Not Cell Is Nothing Then '如果找到
            Str = Cell.Address   '记录单元格地址
            Do
                i = i + 1     '累加变量
                '如果变量等于最后一个参数，那么将查找的单元格右边的值赋予NLOOKUP函数
                If i = 索引号 Then NLOOKUP = Cell.offset(0, 列 - 1): Exit Function
                Set Cell = 区域.Find(查找值, Cell, , xlWhole) '查找下一个
            Loop While Cell.Address <> Str
        Else
            NLOOKUP = NA()     '如果找不到则返回错误值
        End If
     End With
End Function

图3

      如果没有开发工具选项的，可自行添加，方法是依次点击“文件”—“选项”—“自定义功能区”—勾选“开发工具”，如下图：

图4