一篇文章精通 VLOOKUP 函数

相信不少人看到标题，立即嗤之以鼻，VLOOKUP 谁不会？是的，大家都会，但用的好的人不多。相信我，这篇文章一定可以算得上通俗易懂，又有深度的一篇文章，熟练掌握本文所讲内容，一定会在日常 Excel 处理时如鱼得水。

预备知识

Excel 数组

Excel 用 {a,b,c} 或 {a;b;c} 表示数组。数组的主要作用是用于有多个返回值的公式 （数组公式）或函数。我举一个必须用数组公式的例子。假设有三个字段，是按行放置的，我们想进行转置，像下面这样：

要求用函数来实现，不能用选择性粘贴。正确的答案如下图（注意数组函数最后必须 CTRL+SHIFT+ENTER 三个键同时按下，否则错误 ）

注意地址栏是公式有大括号括起来：{=TRANSPOSE(A2:C2)}，这个是 Excel 数组公式的标志。

我们可以把数组简单地理解为：Excel 把工作表的某区域 (Range) 值，按照行列整体在内存中存储，并可以方便的再将数组在工作表的 Range 中进行显示。

来看一个操作，印证和理解一下。在 A1:C3 这个区域存放着两笔信息，我们用数组公式将数据放到同样大小的一块区域 E1:G3

将光标放到编辑栏，按下 F9，对，你没有看错，Excel 对数组使用的是域，所以用 F9 来解析，我们发现，编辑栏变成了这样：

地址栏的数据为： {"ID","姓名","地址";"001","Stone","Wuhan";"002","Tom","England"}，这是一个二维的数组。我们可以看到，行数据用逗号分隔（水平数组），列数据用分号分割（垂直数组）。刚才的操作可以这样解释：Excel 把 A1:C3 的数据拷贝到数组，然后再把数组的数据拷贝到 E1:G3。那么，这样操作的作用是什么呢？注意，这个是理解后续函数操作的关键：我们之所以要用数组公式把数据放到数组中，就是为了在需要在函数中要用到 Excel Range 的地方，也可以用数组来代替，从而获得某种灵活性。现在不理解，不着急，后面有例子说明。

数组的运算

如果把某个值和一个数组进行运算，那么这个值会和数组中的每一个元素（或称每一项）进行运算。如果将两个数组进行运算，那么数组中的每个对应位置的元素都会执行某种运算。这种运算规律非常像线性代数的矩阵运算。

举个例子：

array_computing.gif

IF 函数高级用法

IF 除了返回一个单值，还可以返回一个数组。此时 IF 函数的语法如下：

IF({1,0}, range1, range2)

理解：因为 {1,0} 表示一个数组，所以每一个元素都会执行运算，首先获取第一个元素 1， IF 函数对不为零的条件，返回 range1；然后再获取第二个元素 0, IF 函数对 0，返回 range2。最后，函数再将 range1 和 range2 组合成一个新的数组。

举例: 运用 IF 函数将 ID 和 姓名 两个字段改变顺序

{=IF({1,0},B1:B3,A1:A3)}

VLOOKUP 基本用法

VLOOKUP 的语法如下：

VLOOKUP(Lookup_value, Table_array, Col_index_num, TRUE/FALSE)

• Lookup_value: 要查找的值
• Table_array: 查找的范围。查找的范围中， Lookup_value 对应的列（为了表述方便，后面将对应的列称作关键列）关键列必须处在第一列位置。
• Col_index_num: 查找的列数。在查找范围中，要找的值从第一列算起，所在列的偏移值。比如从 A 算起，如果要在 B 列中查找某值，则列的偏移值为 2，以此类推。要点：查找的列一定要在 Lookup_value 所在列的右边。
• 第四个参数，如果为 FALSE 或者 0，表示精确匹配，如果为 TRUE 或者 非零值，表示模糊匹配

文字描述总是难理解一些，可以参考视频 VLOOKUP 函数 来学习。

接下来介绍 VLOOKUP 函数的几个比较高级一点的用法。

查找列在关键列左边

比如我们要根据 ID 来查找姓名，但不巧 姓名 在 ID 的左边：

我们前面讲过使用 IF 函数构造一个数组，通过这个数组来改变列的位置，也讲过函数需要使用 Range 的地方，可以使用数组来代替。将上面两个技术结合，就可以达到目的。

函数如下：

=VLOOKUP(E2,IF({1,0},B1:B3,A1:A3),2,0)

函数理解： IF({1,0},B1:B3,A1:A3) 返回一个数组，数组内容为：

{"ID","姓名";"001","Stone";"002","Tom"}

所以 VLOOKUP 函数利用这个数组，可以在第二列中就开到对应姓名。

多条件查找

比如我们要根据公司和姓名两个字段来确定人员对应的补助：

方法是将公司和姓名组合成一个字段，然后再使用 VLOOKUP 函数：

H2 单元格的函数为：

{=VLOOKUP(E2&F2,IF({1,0},A1:A5&B1:B5,C1:C5),2,0)}

注意这里必须使用公式数组，同时按下 CTRL+SHIFT+ENTER。

VLOOKUP 和 COLUMNS 函数结合

比如我们要根据补助标准来发放补助，但表的字段太多，补助 在 AJ 列。如果使用 VLOOKUP 函数，需要计算 A 到 AJ 的列数。

这个时候，我们可以通过 columns 函数来帮助我们计算 A 到 AJ 共计多少列。columns 函数的语法如下：

COLUMNS(A:AJ)

结合 VLOOKUP 函数用法如下：

=VLOOKUP(AL2,A:AJ,COLUMNS(A:AJ),0)

自定义 VLOOKUP 函数

如果我们觉得 VLOOKUP 函数复杂，或者想按自己的逻辑来增强 VLOOKUP 函数，我们也可以自定义一个自定义的函数。当然，自定义函数是另一个话题，可以写一系列文章。这里，给出一个并不是太有意义的实现，比如：

VLOOKUPPRO(lookup_value, range1, range2)

根据 lookup_value 在 range1 中找到 lookup_value, 然后返回 range2 对应的值。这样，不用计算列数，一般情况下，我们也用不到模糊查找，我们使用精确查找。

Public Function VLookupPro(lookup_value As Range, table_range1 As Range, table_range2 As Range) As Variant
    Dim result As Variant
    Dim table_array1() As Variant
    Dim table_array2() As Variant
    Dim table_array As Variant

    
    Dim firstRow As Long
    Dim lastRow As Long
    Dim usedRng As Range
    Set usedRng = table_range1.Worksheet.UsedRange
    firstRow = usedRng.Cells(1, 1).Row
    lastRow = usedRng.Cells(usedRng.Rows.Count, 1).Row
    
    ' Populate table_array1 and table_array2
    Dim i As Long
    Dim rowCount As Long
    rowCount = lastRow - firstRow + 1
    ReDim table_array1(1 To rowCount)
    ReDim table_array2(1 To rowCount)
    For i = 1 To rowCount
        table_array1(i) = table_range1.Cells(i, 1).Value
        table_array2(i) = table_range2.Cells(i, 1).Value
    Next
   
    ReDim table_array(1 To UBound(table_array1), 1 To 2)
    For i = 1 To UBound(table_array)
       table_array(i, 1) = table_array1(i)
       table_array(i, 2) = table_array2(i)
    Next
    
    result = Application.WorksheetFunction.VLookup(lookup_value.Value, table_array, 2, False)
    VLookupPro = result
End Function

使用方法示例如下：