Excel VLOOKUP终极教程：基本用法+多条件范例、#NA排除等
Excel VLOOKUP终极教程：基本用法+多条件范例、#NA排除等
VLOOKUP是Excel中使用频率非常高的函数之一，掌握其基本用法和进阶技巧对于提升工作效率至关重要。本文将从基础到进阶，详细讲解VLOOKUP的使用方法，包括基本语法、跨工作表/文件查找、多条件查找、回传多笔资料等实用技巧，并提供常见错误的解决方案。
VLOOKUP全名是「Vertical Lookup」，意为垂直查找数据，是职场上不可或缺的30个函数之一。本文将详细介绍VLOOKUP的用法范例、跨工作表/文件操作、多条件查找、回传多笔资料、比对两工作表差异等进阶用法，以及如何排除#NA等常见错误。
VLOOKUP函数基本用法
以下是VLOOKUP函数的语法说明：
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
引数名稱 | 解釋 | 範例 |
|---|---|---|
lookup_value | 指定查找资料的名称或储存格位置 | “吳芝正” |
table_array | 選定欲查找之範圍 | A:B |
col_index_num | 想回傳第幾欄？ | 2 |
[range_lookup] | 0：完全符合／1：部分符合（选填） | 0 |
使用范例：假设A栏是学生姓名，B栏是分数。我们要查找“吴芝正”，并回传分数，VLOOKUP函数可以这样写：
=VLOOKUP("吳芝正",A:B,2,0)
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文件的操作也一样，输入第二个引数时直接换到另一个Excel文件拉范围就好。
多条件查找资料（两栏条件）
假设我们今天有各家服饰单品的价格，但今天我们想要回传同时符合“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到底该回传第几个栏位，如下图：
如果想要查找同时符合“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只会回传一笔资料。但如果你的Excel是支持“动态阵列”版本（2021或365以上），就可以利用动态阵列一次回传多个栏位！
使用范例：利用VLOOKUP回传“李大华”的“部门”与“薪水”：
=VLOOKUP("李大华",A:C,{2,3},0)
{ 2,3 } 表示“先回传第2栏，再回传第3栏”。而如果要回传更多笔资料，直接在矩阵里面继续新增栏位即可：
使用范例：利用VLOOKUP回传“李大华”的“部门”、“薪水”、“评级”：
=VLOOKUP("李大华",A:D,{2,3,4},0)
比对两个工作表差异
我们可以利用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。
VLOOKUP温馨提醒
- 如果资料是呈水平排序，可以将资料旋转贴上（又称为转置）再执行VLOOKUP，或者使用HLOOKUP函数
- 在输入下一个引数时记得加上“,”
- 如果有重复的资料名称，VLOOKUP只会回传第一个找到的资料
- 选取范围时，要特别留意有没有栏位被隐藏