资讯

历史

科技

环境与自然

成长

游戏

财经

文学与艺术

美食

健康

家居

文化

情感

汽车

三农

军事

旅行

运动

教育

生活

星座命理

Excel VLOOKUP终极教程：基本用法+多条件范例、#NA排除等

创作时间:

作者:

@小白创作中心

Excel VLOOKUP终极教程：基本用法+多条件范例、#NA排除等

引用

来源

https://meowmeowwantme.com/vlookup-ultimate-guide/

VLOOKUP是Excel中非常重要的一个函数，主要用于在表格或区域中按行查找数据。本文将详细介绍VLOOKUP函数的基本用法、跨工作表/文件查找、多条件查找、回传多笔数据、比对两个工作表差异等进阶用法，以及如何排除常见错误。

VLOOKUP 函数基本用法

以下是 VLOOKUP 函数的语法说明：

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

引数名稱	解釋
lookup_value	指定查找资料的名称或储存格位置
table_array	選定欲查找之範圍
col_index_num	想回传第几栏？
[range_lookup]	0：完全符合／1：部分符合（选填）

使用示例：假设 A 列是学生姓名，B 列是分数。我们要查找“吴芝正”，并回传分数，VLOOKUP 函数可以这样写：

=VLOOKUP("吴芝正",A:B,2,0)

如果还是不太懂的话，那我们来各个了解 VLOOKUP 的4个引数的规则与限制。

一、lookup_value（指定查找资料名称）

第一个引数要输入欲查找资料的“名称”或是“储存格位置”。

我们在 E2 中输入“=VLOOKUP”，并且接著选定要查找的储存格“D2”（或者输入“吴芝正”）：

=VLOOKUP(D2,
=VLOOKUP("吴芝正",

二、table_array（选定查找范围）

第二个引数，我们可以选定查找的范围，告诉 Excel：我要在这个范围内找到第一个引数输入的资料。这个范围可以在同一张工作表内，不同工作表，甚至是不同的活页簿（档案）之间。

接续前面的例子，我想要查找的范围是该工作表的 A 到 B 列，因此 VLOOKUP 函数的第二个引数就要拉一个“A:B”：

注意！VLOOKUP 有一个很强硬的限制：第一个引数一定要在查找范围的最左边。沿用前面的范例，既然我们第一个引数输入了“吴芝正”，那范围就一定得要从姓名的栏位开始拉，否则就会回传 #N/A!：

=VLOOKUP("吴芝正",A:C,3,0) //显示#N/A!

三、col_index_num（选定回传第几栏）

选完范围之后，我们要告诉 Excel 要回传范围中的第几栏。

在这个例子中，我想要回传的是学生的分数，也就是范围中的第 2 栏，那么我就要输入“2”：

注意！是“范围中的第几栏”，而不是“在这张工作表的第几栏”！是相对位置的概念，不是绝对位置。

四、[range_lookup]（完全符合/部分符合）

第 4 个引数，我们要确认资料的比对方式，要“完全符合”还是“部分符合”：

完全符合：输入 0 / FALSE
部分符合：输入 1 / TRUE / 不输入

以下会分别解释“完全符合”和“部分符合”的用法与差异。

完全符合（0 或 FALSE）

基本上，大多数情况都适用“完全符合”，也就是 VLOOKUP 查找资料名称时一定要找一字不差的值，直到找到后才会回传值。

这也代表资料名称一定要存在而且名称相同，就算差了一个空格也不行，否则就会回传 #N/A!，发生找不到相符资料的情况。

要设定完全符合，我们只要在 VLOOKUP 的第4个引数输入“0”或者“FALSE”就可以了：

=VLOOKUP(D2,A:B,2,0)

部分符合（1 或 TRUE）

部分符合就比较复杂了。部分符合的逻辑是：找出最接近且小于等于查找资料的“数值”。

使用示例：假设我们今天想要知道用 400 块最多能买多少入的马桶清洁碇，那么我们就可以利用“部分符合”找出最接近 400 的值并且回传数量。以下为详细的步骤：

STEP. 1：我们先将网路上蒐集到的定价贴在 Excel 上（A 与 B 列）。

STEP. 2：用 Excel 的“排序与筛选”功能将资料的 A 列由小到大排序。

注意！将资料由小到大排序非常重要，否则 VLOOKUP 最后回传的值会是错的。（因为 VLOOKUP 的查找逻辑是垂直由上往下找，只要找到超过 400 的值就停止查找，所以我们必须要把资料由小到大排序）

STEP. 3：将资料由小到大排序之后，就可以在 VLOOKUP 的第 4 个引数中输入“1”或是“TRUE”囉：

=VLOOKUP(D2,A:B,2,1)

设定“部分符合” 的 VLOOKUP 函数会先找到最接近且小于等于 400 的值，也就是本例的“399”，接着再回传 B 列的“30入”。

VLOOKUP 不同工作表 / 不同档案查找资料

VLOOKUP 函数在“跨工作表”与“跨档案”的操作其实跟在同一张工作表的步骤一模一样。

可以参考以下的 GIF ：

详细操作步骤为：

输入 VLOOKUP 函数
第一个引数：点一下“吴芝正”。
第二个引数：直接换到另一个工作表，拉想要查找的范围（记得欲查找的资料一定要在范围的最左边）。
第三个引数：输入要回传范围中的第几栏。
第四个引数：输入0（完全符合）或1（部分符合）。
按下Enter

引数名稱	解释	範例
lookup_value	指定查找资料的名称或储存格位置	B3 或 “吴芝正”
table_array	選定欲查找之範圍	全校成绩单!B:C（“全校成绩单”工作表的 B 到 C 列）
col_index_num	想回传第几栏？	2
[range_lookup]	0：完全符合／1：部分符合（选填）	0

而跨 Excel 档案的操作也一样，输入第 2 个引数时直接换到另一个 Excel 档案拉范围就好。

VLOOKUP 进阶用法 1 – 多条件查找资料（两栏条件）

假设我们今天有各家服饰单品的价格，但今天我们想要回传同时符合“GOOPI”与“帽T”的价格。我们可以怎么做呢？

其实我们只要“将 2 栏併成 1 栏”，VLOOKUP 就可以一次查找 2 个条件的资料。

Step.1：我们先在资料左边新增一栏“厂牌&单品”的栏位。

Step.2：利用 CONCAT 函数（旧称 CONCATENATE）合併厂牌与单品的名称：

=CONCAT(B2,"-",C2)

Step.3：最后再利用 VLOOKUP 查找连接后的资料名称就可以一次查找两个条件了：

=VLOOKUP("GOOPI-帽T",A:D,4,0)

VLOOKUP 进阶用法 2 – 多条件查找资料（十字条件）

有时候资料一多起来，我们就会不知道 VLOOKUP 到底该回传第几个栏位，如下图：

如果想要查找同时符合“RO逆渗透滤水器”以及“Aug”的资料，可以怎么做呢？

其实，我们可以利用 VLOOKUP+MATCH 函数，直接回传 8 月的资料，而不需要数出 8 月在第几栏。

Step. 1：利用 MATCH 函数先算出“Aug”位于范围中的第几个栏位：

=MATCH("Aug",A1:M1,0)

Step. 2：取得“Aug”位于第几个栏位后，将这段 MATCH 函数丢进 VLOOKUP 回传栏位的引数中：

=VLOOKUP("RO逆渗透滤水器",A1:M6,MATCH("Aug",A1:M1,0),0)

VLOOKUP 进阶用法 3 – 回传多笔资料

一般情况下，VLOOKUP 只会回传一笔资料。

但如果你的 Excel 是支持“动态阵列”版本（2021 或 365 以上），就可以利用动态阵列一次回传多个栏位！

使用示例：利用 VLOOKUP 回传“李大华”的“部门”与“薪水”：

=VLOOKUP("李大华",A:C,{2,3},0)

{ 2,3 } 表示“先回传第 2 栏，再回传第 3 栏”。而如果要回传更多笔资料，直接在矩阵里面继续新增栏位即可：

使用示例：利用 VLOOKUP 回传“李大华”的“部门”、“薪水”、“评级”：

=VLOOKUP("李大华",A:D,{2,3,4},0)

VLOOKUP 进阶用法 4 – 比对两个工作表差异

我们可以利用 VLOOKUP 找不到完全符合的资料名称时，会回传 #N/A 的原理，来找两笔资料的差异。

使用示例：假设现在有资料 A 与资料 B 两笔资料，我们想要确认资料 B 中的每一笔资料在资料 A 中有没有出现过：

Step. 1：在资料 B 的比对结果中，输入 VLOOKUP 函数，来查找资料 A 中的“apple”，并回传第 1 栏：

=VLOOKUP(A2,资料A!A:A,1,0)

Step. 2：接著，将 VLOOKUP 函数丢进 ISNA 函数，将“#N/A”回传“TRUE”，将“非 #N/A”回传 FALSE：

=ISNA(VLOOKUP(A2,资料A!A:A,1,0))

Step. 3：再把 ISNA 放进 IF 函数的第一个引数中，让 IF 函数去判断如果为 TRUE，则回传“X”，否则回传“V”。

=IF(ISNA(VLOOKUP(A2,资料A!A:A,1,0)),"X","V")

VLOOKUP 函数排除 #N/A 方法

以下列举了我遇到的 4 种出现 #N/A 的原因与解决方法，如果你有发现其他可能导致 #N/A 的原因，也都非常欢迎留言和我讨论。

可能性 1：VLOOKUP 确实查无相符资料，有可能是由于资料打错字而没有发现。

解决方法：针对出现#N/A的资料名称片段，利用寻找与替换功能（CTRL+F）去确认是否有相符资料。

可能性 2：资料中有出现多余空格而没有发现（如文字的最后面），造成“有资料却找不到”的情况出现。

解决方法：用 TRIM 函数将多余的空格移除后再查找。

=TRIM(A3)

可能性 3：VLOOKUP 的查找范围最左边并不是要查找的资料。

解决方法：重新设定函数，要查找的资料一定要在查找范围的最左边。

可能性 4：可能是资料格式的问题。如 GA4 下载的数据有时候 VLOOKUP 就是找不到。（这个真的会发疯）

解决方法：建议可以开启新的栏位或工作表，并且用“=A1”、“=A2”、“=A3”，或者结合 TEXT 或 VALUE 函数将资料传到其他的地方。

VLOOKUP 常见错误（#N/A、#REF! 、#VALUE!）

#N/A：指 VLOOKUP 函数找不到对应的资料，此时应该注意大小写、字之间的空白格，或者确定欲查找的资料名称确实存在。
#REF!：通常发生在 col_index_num 的值> table_array 栏数，或者原本的资料有遭到更动或删除过。
#VALUE!：通常发生在 col_index_num 的值 <1时，或者 [range_lookup] 没有办法被有效判读是 TRUE 还是 FALSE。