標籤彙整:vlookup

EXCEL函數 vlookup

使用情境

手上有一筆清單及資料庫如下

資料庫

A B
中文 日文
紅色 レッド
藍色 ブルー
綠色 グリーン
黃色 イエロー
黑色 ブラック
白色 ホワイト
棕色 ブラウン
粉紅色 ピンク

資料清單

C
紅色
藍色
棕色

想要資料清單自動比對出日文翻譯,如下

比對後的資料清單

C D
紅色 レッド
藍色 ブルー
棕色 ブラウン

vlookup參數解説

vlookup共有4個値:

=vlookup(①清單一列, ②資料庫多列或一列, ③目標資料的欄數, ④結果回報)
  • vlookup只能比對縱向列資料,如果是橫向欄資料必須使用hlookup比對
  • ①參數:資料清單,也就是【C:C】
    C
    紅色
    藍色
    棕色
  • ②參數:資料庫,可以選取多欄或單欄,這邊選取兩欄,即為【A:B】
    A B
    中文 日文
    紅色 レッド
    藍色 ブルー
    綠色 グリーン
    黃色 イエロー
    黑色 ブラック
    白色 ホワイト
    棕色 ブラウン
    粉紅色 ピンク
  • ③參數:尋找値在②參數的第幾欄中。本例要找的日文在資料庫的第2欄,於是填【2】
  • ④參數:填【0】,代表資料庫找不到的話回傳N/A

所以本例的函數要寫成

=vlookup(C:C, A:B, 2, 0)

算好後按儲存格的右下角小十字,自動填滿整欄
可以得出

C D
紅色 レッド
藍色 ブルー
棕色 ブラウン

補充

  • 結果欄D欄充滿函數,導致檔案變得很大,可以用選擇性貼上【貼上値】,使檔案輕量化
  • vlookup法只能回報單一結果資料,若要回報多重結果,可以參考這篇

EXCEL 比對多元資料方法

原理

excel中的vlookup函數只能用找單一資料,也就是當手上有一筆清單及資料庫如下

資料庫

中文 日文
紅色 レッド
藍色 ブルー
綠色 グリーン
黃色 イエロー
黑色 ブラック
白色 ホワイト
棕色 ブラウン
粉紅色 ピンク

資料清單

紅色
藍色
棕色

經過vlookup比對後可以得出

比對後的資料清單

紅色 レッド
藍色 ブルー
棕色 ブラウン

但當資料庫非一對一情況下,vlookup只能隨機找到一個對應資料,無法全部顯示

資料庫:單一資料擁有多個對應資料

中文 日文
紅色 レッド
紅色 ボルドー
紅色 ダークレッド
藍色 ブルー
藍色 ネイビー
綠色 グリーン
黃色 イエロー
黑色 ブラック
白色 ホワイト
棕色 ブラウン
棕色 ショコラ
棕色 モカ
粉紅色 ピンク

解決方法

  1. 下載充滿函數的excel檔案
  2. 將資料庫部分貼在B跟C欄
  3. 清單貼在E欄

可以找出所有相關資料

紅色 レッド ボルドー ダークレッド
藍色 ブルー ネイビー
棕色 ブラウン ショコラ モカ