[Excel] 如何使用VLOOKUP比對兩組資料?

by Posted on

在工作上我們經常會遇到兩大堆資料中，要去做驗證、比對、找出新增的項目、找出漏掉的項目、抓出打錯的部分、抓出特殊的某筆資料等，其實這些情境我們可以簡單的利用你常用的VLOOKUP功能來查找，詳細可以看下面的範例。

問題一：公司目前重新整理客戶名冊，客戶名冊大概有上百筆，主管希望我找出新的客戶名冊中，哪些客戶是公司的老客戶？

  • 許多人會使用一筆一筆資料比對的方式或是用Ctrl+F去查找，但若清單中有數百筆資料，除了浪費時間外，也有很有可能出錯，因此善用我們最熟悉的Vlookup來比對是一個很好的辦法。
  • 假設我們拿到的新舊客戶清單如下表，舊客戶清單為A2至A7, 新客戶清單為B2至B7。
excel vlookup new client and old client table.

此時請在往後一欄設定公式：

=VLOOKUP(欲比對的欄位, 原始資料範圍, 回傳第幾行的值, 完全比對或不完全比對)

此範例的公式為 (結果如下圖紅色字)：

=VLOOKUP(B2,A$2:A$7,1,0)
註：公式打好，記得要用滑鼠在格子右下角顯示+號的地方往下拉到最後一格。

說明：

  • =VLOOKUP(我現在要比B2的值,
  • 整個舊客戶的資料是從A2到A7但因為我若待會直接用滑鼠按表格右下角+往下拉數字可能會跑掉，因此我在數字前加上金錢$符號防止數字跑掉A$2:A$7,
  • 填1因為我要回傳的值在第一行，若你有很多行就要看你要回傳的值是哪一行,
  • 填0因為我希望兩組值是完全比對)
  • 看到下圖為產出的結果，有顯示Apple代表函示在A2:A7之間找到了相同的Apple這個字、Dell、Acer亦相同意思
excel vlookup new client and old client table.

問題二：主管說，你這樣我看不懂，再顯示一次品牌名稱是什麼意思？還有我不要在表格上看到亂碼，給我改成看得懂的東西!!

好，解決這個問題也不難，我們再套用一個公式即可。

=IF(ISNA(剛才原本寫下的VLOOKUP公式),"如果顯示#N/A時你希望顯示的文字","如果有在清單中找到相同資料(老客戶)你要顯示的文字")

因此在這題，我們就可以把公式寫成這樣：

=IF(ISNA(VLOOKUP(B2,A$2:A$7,1,0)),"查無此客戶","此客戶為老客戶")
註：公式打好，記得要用滑鼠在格子右下角顯示+號的地方往下拉到最後一格。

如此一來你就可以得到如下圖的結果啦！

excel vlookup new client and old client table + vlookup公式

這個案例也可以延伸到各種情境，例如尋找兩次報名的學員是否重複、尋找抽獎中獎者是否重複中獎、比對兩批訂單資料是否重複key單、比對單號是否存在、以會員編號尋找新舊會員數跟名單、上百顆料號中尋找客戶需要的料件、以Excel管理的庫存清單表快速查找大批庫存數量等等，非常非常多的情境都可以用上述方案去套用喔。

若還有其他疑問，歡迎使用右上角CONTACT US寫信給我們唷!

PS. 本篇適用範圍包含Microsoft Excel單機版、Office 365線上版, Google Sheets試算表

Published by impochun