VLookup函数使用大全

."VLookup" "HLookup" 函数的使用今天我们继续. 今天我们要学习的是我个人认为Excel 中最重要的一个函数 (至少我的工作是这样). 隆重介绍:VLookup & HLookup

."VLookup" "HLookup" 函数的使用

今天我们继续. 今天我们要学习的是我个人认为Excel 中最重要的一个函数 (至少我的工作是这样). 隆重介绍:

VLookup & HLookup

大家看到"Lookup" 就应该知道这个函数何查找有关系. 没错. 这个函数就是让我们找东西的.

VLookup 中的 "V" 是的意思, 也就是说这个函数是竖着找; H是Horizontal, 就是横着找. 我们下面就只讲VLookup 函数, 大家学会了自然就知道HLookup 怎么用了. 而且如果做数据分析, 我们大部分都是用VLookup.

好, 我们先来看看VLookup 函数有哪些参数:

= Vlookup (参数1, 参数2, 参数3, 参数4)

例如: B2 = Vlookup(A2, E:F, 2, False)

参数1: (要找什么?) 你要查找的单元格或者内容, 比如下面的A2单元格

参数2: (到哪里找?) 你要到哪里找出你想要的内容, 比如上面的函数我们是希望从E 列中找到A2, 然后从F 列中把相应的Offer 数取出来.

参数3: (列的间隔数) 你" 要找的数据" 和" 希望得出的数据" 相距的列数, 比如上面的例子, 参数3是"2", 因为E 和F 为间隔的两列, 所以是2. 如果是E 列到G 列, 那么就是3了.

参数4: 有TRUE 和FALSE 两个选择, FALSE是精确匹配, 只有完全相同的内容他才找出来; TRUE则可以找相似的内容. (建议大家不要用TRUE, 因为不知道他到底能找到什么)

所以上面的函数(Vlookup(A2, E:F, 2, False)) 的意思就是: 在B2单元格中, 我要把A2单元格的内容拿出来, 到E 列里面找有没有和A2中一样的内容, 如果有, 则把E 列的这个单元格(E2)所对应的F 列(F2) (因为参数3是2) 取出来放在单元格B2中. 我们看看结果如下:

这么说估计大家还是不是很理解到底这个函数干吗用. 好, 下面我们开始举例子:

例子1: 公司进行了一次考试, A14-A17是4个不及格的Agent 的名单, 老板叫你去把这四人的Team 查一查.

这个时候呢, 你当然可以一个个看, 因为才4个人, 但是当人多的时候就要用到函数了. 所以你要先去找HR 或者是Reporting Team要一份Agent 和Team 的对应表. 假设你从HR 那里拿到一份表单如E13:G22, 是所有员工的信息.

好, 这样我们就可以开始在B 列找出这四个Agent 相应的Team 了.

以A14(Jack)为例:

- 我们要找什么? 找"Jack", 就是找A14单元格 (参数1 = A14)

- 我们要到哪里找? 我们要从E13:E22里面找A14的内容(Jack), 然后从G13:G22中取出相应行的Team 名字 (参数2 = E13:G22)

- 参数2里面的" 要找的数据" 和" 希望得出的数据" 相距多少列? E/F/G, 三列 (参数3 = 3)

- 我们要精确匹配 (参数4 = FALSE)

所以B14可以写成: = VLOOKUP(A14,E13:G21,2,FALSE) 大家可以看到结果如下:

好, 接下来我们只要把B14的函数直接往下拖就可以得到B15:B17的结果了. 如下:

大家注意到没有? 前三个Agent 所对应的Team 都找到了, 为什么第四个Agent - Dibort为什么出错了呢? 我们明明看到E15单元格里面是Dibort 啊???

大家注意看上面的图, 注意一下C14 - C17几个函数的区别! 大家看到吗? 这四个函数的第二个参数随着我们刚才拖动函数而变化了. 从B14单元格中的"E13:G21"变成了B17中的"E16:G24". (这个是Excel 的自有功能, 函数中的参数会随着我们的拖动或者拷贝而自动变化), 当然参数1也是自动变化的.

其中参数1的变化是我们需要的, 因为我们要找的是A14-A17的内容; 但是参数2我们并不希望他变化, 因为我们要的数据是应该固定在E13:G21不变.

那么我们如何固定参数2呢? 在Excel 中, 如果要让参数固定, 则要用到"

鬼推磨, 我们就用" 美金" 让参数固定. $" 这个符号. 俗话说: 有钱能使

现在我们把B14改一下: B14 = VLOOKUP(A14,$E$13:$G$21,3,FALSE)

大家看到了. 我们在需要固定的地方加上"$", 当我们拖动函数时这些参数就不会再变了. 对于这个例子, 其实我们可以看到其实只有行号在变, 列号(E,F)其实没有变化, 所以这里我们写成 " E$13:G$21 " 也是可以的.

*** 这里我们讲个小技巧, 其实当我们加"$"挺麻烦的, 因为特殊字符我们都不常用. 这里我们可以点击函数编辑框, 光标移到相应的位置, 然后按"F4", 就可以直接在行和列上加上$了, 再按"F4" 就会去掉列的$, 只保留行的$; 再按"F4", 则变成只有列有$; 再按"F4", 所有的$都消失了. (大家自己试一试就明白了)

例子2: 如下表, A列是所有Agent 的名单, B列需要找出每个人今年的Bonus. 老板今天和你说: 今年经济危机了, 只有4个人有Bonus(就是E30:E33这4个人), 让你去把B 列填清楚. 对于有Bonus 的Agent 则显示" 裁员广进" Bonus的数目, 没有Bonus 的显示" 薪饷四成".

那么我们一步步来. 先用Vlookup 函数找找看.

B30 = VLOOKUP(A30,$E$29:$F$33,2,FALSE)

结果如图, 我们看到, 其中4个Agent 在E 列找到了, 所以显示出了相应的Bonus, 其余的Agent 因为没有找到所以得出了错误值"#N/A".

但是我们题目的要求不是这么简单, 还要显示出相应的文字, 因此我们要用到 ISNA 函数来判断我们得来的值. 然后用 IF 函数显示相应的文字.

B30 = IF(ISNA(VLOOKUP(A30,$E$29:$F$33,2,FALSE)),"薪饷四成"," 裁员广进

"&VLOOKUP(A30,$E$29:$F$33,2,FALSE))

对于Vlookup 函数的应用, 大家特别要注意的是要加上$以讲查询范围固定, 经常的错误就是这个造成的. (当然, 有的时候反而不需要固定, 要看具体需要而定).

例子3

: 其中"F15:G21"是每个Agent 的英文成绩; "I15:J21"是法语成绩, 请在B 列中显示: 如果总成绩高于100, 则显示Pass, 低于100显示Fail

要计算英文和法语的成绩总和, 则需要分别找到英文的成绩和法语的成绩. 所以函数就是:

查找英文成绩: VLOOKUP(A16,$F$15:$G$21,2,FALSE)

查找法语成绩: VLOOKUP(A16,$I$15:$J$21,2,FALSE)

后面就简单了吧. 直接用IF 语句来判断是否这两个成绩的和是否大于100. 因此整个函数就是: B16 =

IF(VLOOKUP(A16,$F$15:$G$21,2,FALSE) VLOOKUP(A16,$I$15:$J$21,2,FALSE)>=100,"Pass","Fail")

例子4: 让我们来练习一下HLOOKUP 函数好了. 如下面的两个表, 请用HLOOKUP 函数查找每个人的号码.

这个就不多说了. 按照Vlookup 的用法直接套用就可以了.

B31 = HLOOKUP(A31,$E$30:$K$34,5,FALSE)

为什么第三个参数是5? 自己想了~~~ 呵呵

例子5: 下面的B46和B48分别可以选择名字和语言, 请根据这两项的选择自动在B50中显示相应的成绩(成绩在右边的表中)

好, 这题我们要分两步走, 第一步, 我们可以假设没有B48的条件, 而是单纯查找法语成绩, 这样就简单了. 函数可以写成:

B50 = VLOOKUP(B46,$F$47:$H$52,3,FALSE)

很简单, 第一步就完成了. 但是怎么根据B48来决定查找呢? 就是要通过B48来决定上面函数的第三个参数 - 3???? 看到上面的表, 大家可以知道要查找英文则参数3等于2; 要查找法语则参数3等于3. 所以上面的参数3可以替换成:

= IF(VLOOKUP("Name",$F$46:$H$46,2,FALSE)=B48,2,3)

把两个函数合在一起就是:

B50 =

VLOOKUP(B46,$F$47:$H$52,IF(VLOOKUP("Name",$F$46:$H$46,2,FALSE)=B48,2,3),FALSE)

结果如下

:

LOOKUP 查找函数（一）

2010年02月12日 8:25

含义：返回向量或数组中的数值。函数 LOOKUP 有两种语法形式：向量和数组。函数 LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值；函数 LOOKUP 的数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。

函数 LOOKUP 有两种语法形式：向量和数组。

提示： LOOKUP_vector 的数值必须按升序排序：... 、-2、-1、0、1、2、... 、A-Z 、FALSE 、TRUE ；否则，函数 LOOKUP 不能返回正确的结果。文本不区分大小写。

一、语法 1（向量）

向量为只包含一行或一列的区域。函数 LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。如果需要指定包含待查找数值的区域，则可以使用函数 LOOKUP 的这种形式。函数 LOOKUP 的另一种形式为自动在第一列或第一行中查找数值。

LOOKUP(lookup_value,lookup_vector,result_vector)

Lookup_value 为函数 LOOKUP 在第一个向量中所要查找的数值。

lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。

Lookup_vector 为只包含一行或一列的区域。lookup_vector 的数值可以为文本、数字或逻辑值。

Result_vector 只包含一行或一列的区域，其大小必须与 lookup_vector 相

同。

说明

• 如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。

• 如果 lookup_value 小于 lookup_vector 中的最小值，函数 LOOKUP 返回错误值 #N/A。

示例

如果您将示例复制到空白工作表中，可能会更易于理解该示例。

A B

1 频率 颜色

2 4.14 幽红

3 4.91 轻橙

4 5.17 艳黄

5 5.77 湖青

6 6.39 醺蓝

公式 说明（结果）

=LOOKUP(4.91,A2:A6,B2:B在A 列中查找4.91，并返回同一行B

6) 列的值(轻橙)

=LOOKUP(5.00,A2:A6,B2:B在A 列中查找5.00（最接近的下一个

6) 值为4.91），并返回同一行B 列的值(轻橙)

=LOOKUP(7.66,A2:A6,B2:B在A 列中查找7.66（最接近的下一个

6) 值为6.39），并返回同一行B 列的值(醺蓝)

在A 列中查找0，由于0小于查找向量=LOOKUP(0,A2:A6,B2:B6) A2:A7中的最小值，所以返回错误值(#N/A)

LOOKUP 查找函数（二）

2010年02月12日 8:29

二、语法 2（数组）

函数 LOOKUP 的数组形式是在数组的第一行或第一列中查找指定数值，然后返回最后一行或最后一列中相同位置处的数值。如果需要查找的数值在数组的第一行或第一列，就可以使用函数 LOOKUP 的这种形式。当需要指定列或行的位置时，可以使用函数 LOOKUP 的其他形式。

LOOKUP(lookup_value,array)

Lookup_value 为函数 LOOKUP 在数组中所要查找的数值。LOOKUP_value 可以为数字、文本、逻辑值或包含数值的名称或引用

• 如果函数 LOOKUP 找不到 lookup_value，则使用数组中小于或等于 lookup_value 的最大数值。

• 如果 lookup_value 小于第一行或第一列（取决于数组的维数）的最小值，函数 LOOKUP 返回错误值 #N/A。

Array 为包含文本、数字或逻辑值的单元格区域，它的值用于与

lookup_value 进行比较。函数 LOOKUP 的数组形式与函数 HLOOKUP 和函数 VLOOKUP 非常相似。不同之处在于函数 HLOOKUP 在第一行查找

lookup_value，函数 VLOOKUP 在第一列查找，而函数 LOOKUP 则按照数组的维数查找。

• 如果数组所包含的区域宽度大，高度小（即列数多于行数），函数 LOOKUP 在第一行查找 lookup_value。

• 如果数组为正方形，或者所包含的区域高度大，宽度小（即行数多于列数），函数 LOOKUP 在第一列查找 lookup_value。

• 函数 HLOOKUP 和函数 VLOOKUP 允许按行或按列索引，而函数 LOOKUP 总是选择行或列的最后一个数值。

示例

如果您将示例复制到空白工作表中，可能会更易于理解该示例。

公式 说明（结果）

在数组的第一行中查找

=LOOKUP("C",{"a","b","c","d";1,2,3,4}) “C”，并返回同一列中最后一行

的值(3)

在数组的第一行中查找

=LOOKUP("bump",{"a",1;"b",2;"c",3}) “bump”，并返回同一行中最后一

列的值(2)

VLOOKUP 与HLOOKUP 都是查找函数，不过VLOOKUP 是按列查找的，HL OOKUP 是按行查找的。具体使用方法如下：

一、VLOOKUP 函数【按列查找】

VLOOKUP(lookup_value,table_array, col_index_num,range_lookup)

VLOOKUP（查找值，区域，列序号，逻辑值）

例如：=VLOOKUP($C5,记录单!$C$3:$D$5000, 2,FALSE)

1）lookup_value（查找值）：为需要在表格数组第一列中查找的数值。Lookup_value可以为数值或引用。若lookup_value小于table_array第一列中的最小值，VLOOKUP 返回错误值#N/A。

2）table_array（区域）：为两列或多列数据。使用对区域或区域名称的引用。table_array第一列中的值是由lookup_value搜索的值。这些值可以是文本、数字或逻辑值。文本不区分大小写。

3）col_index_num（列序号）：为table_array中待返回的匹配值的列序号。col_index_num为1时，返回table_array第一列中的数值；col _index_num为2，返回table_array第二列中的数值，以此类推。如果co l_index_num小于1，VLOOKUP 返回错误值#VALUE!。大于table_array的列数，VLOOKUP 返回错误值#REF!。

4）range_lookup（逻辑值）：指定希望VLOOKUP 查找精确的匹配值还是近似匹配值。

如果为TRUE （或为1），则返回精确匹配值或近似匹配值。也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。table_array 第一列中的值必须以升序排序；否则VLOOKUP 可能无法返回正确的值。有关详细信息，请参阅排序数据。