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

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

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

  • 許多人會使用一筆一筆資料比對的方式,但若清單中有數百筆資料,除了浪費時間外,也有很有可能出錯,因此善用我們最熟悉的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因為我希望兩組值是完全比對)
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公式

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