VLOOKUP

 
如果試算表上有已知的資訊，你可以使用 VLOOKUP 逐列搜尋相關資訊。舉例來說，如果要買橘子，可以使用 VLOOKUP 搜尋價格。
VLOOKUP formula example
適用於 BigQuery 的 VLOOKUP

縱向查詢。在搜尋欄中尋找相符項目，然後傳回該相符項目在指定資料欄中的對應值。

用法示範

VLOOKUP("蘋果",table_name!fruit,table_name!price)

語法

VLOOKUP(搜尋值, 範圍,索引, 已排序)

  • 搜尋值：要在搜尋欄中搜尋的值。
  • 搜尋欄：搜尋值所在的資料欄。
  • 結果欄：結果值所在的資料欄。
  • 已排序：[選用] 尋找與搜尋值相符之項目的方法。
    • FALSE：如要採用完全比對方法，建議指定這個選項。
    • TRUE：在未指定「已排序」引數的情況下，這是預設值 (採用近似比對方法)。
      提示：採用近似比對方法前，請先依遞增順序排序搜尋值資料欄。否則，可能會傳回不正確的值。請參閱這篇文章，瞭解為何會傳回不正確的值。

提示：如需更有彈性的 BigQuery 資料庫查詢功能，請使用 XLOOKUP。

語法

=VLOOKUP(搜尋值, 範圍, 索引, [已排序])

需要指定的引數

  1. 搜尋值：要在指定範圍的第一欄中搜尋的值。
  2. 範圍：要搜尋的範圍。
  3. 索引：指定範圍中傳回值所在的資料欄索引。索引必須是正整數。
  4. 已排序：選填。請選擇下列任一選項：
    • FALSE = 完全比對。這是建議選項。
    • TRUE = 近似比對。在未指定「已排序」引數的情況下，這是預設值。
      重要事項：採用近似比對方法前，請先依遞增順序排序搜尋值資料欄。否則，可能會傳回不正確的值。請參閱這篇文章，瞭解為何會傳回不正確的值。

傳回值

在所選範圍中找到的第一個相符值。
語法詳細說明：
範例：
=VLOOKUP(G9, B4:D8, 3, FALSE)
=VLOOKUP("蘋果", B4:D8, 3, TRUE)
需要輸入的引數 說明
搜尋值
這是你要在指定範圍的第一欄中搜尋的值。如要避免傳回錯誤值，你要搜尋的值就必須位於指定範圍的第一欄。你也可以輸入儲存格參照。
簡單的檢查方法：如果搜尋值位於 B3，則範圍的第一欄就必須是 B 欄。
範圍
這個範圍是指：
  • 函式會搜尋的範圍，此範圍的第一欄中含有指定的搜尋值。
  • 索引指定的資料欄 (含有 VLOOKUP 的傳回值) 所在的範圍。你也可以使用已命名範圍。
如要避免傳回錯誤值，你要搜尋的值就必須位於指定範圍的第一欄。
簡單的檢查方法：如果搜尋值位於 B3，則範圍的第一欄就必須是 B 欄。
索引
這是指範圍中含有傳回值的資料欄的索引，也稱為「欄編號」。
  • 可能的最小索引值是 1。
  • 可能的最大索引值則是該範圍的最大欄數值。
設定好範圍後，VLOOKUP 就只會查找搜尋值所在的資料欄 (如果索引 = 1)，或更右側的資料欄。
提示：使用 VLOOKUP 時，請設想範圍中的資料欄是由左至右從 1 開始編號。
已排序
這是選填引數，可用的兩個選項為 TRUE 和 FALSE。
  • 如果已排序為 TRUE，則 VLOOKUP 會採用近似比對方法。
    重要事項：採用近似比對方法前，請先依遞增順序排序搜尋值資料欄。否則，系統可能會傳回非預期值。請參閱這篇文章，瞭解為何會傳回不正確的值。

  • 如果已排序為 FALSE，則 VLOOKUP 會採用完全比對方法。

  • 如未指定已排序引數，則預設為 TRUE。
強烈建議的做法：
  • 將已排序引數設為 FALSE：因為無論搜尋值資料欄是否已排序，系統的查找行為都一致。
  • 即使已排序是選用引數，也一律加以設定來提高函式的可讀性。

 

輸出結果 說明
傳回值
這是 VLOOKUP 根據你輸入的引數傳回的值，每個 VLOOKUP 函式只有一個傳回值。
  • 如果範圍中有多個與搜尋值相符的值，系統會傳回找到的第一個相符值的對應傳回值。
  • 如果傳回 #N/A，表示找不到相符值。
如果系統傳回非預期值、#N/A 或 #VALUE! 等錯誤，請著手排解問題。如要將傳回的「#N/A」替換為其他值，請參閱這篇文章，進一步瞭解如何搭配 IFNA() 使用 VLOOKUP()。

VLOOKUP 基本範例：

使用 VLOOKUP 查找不同搜尋值

使用 VLOOKUP 查找橘子和蘋果的價格。

VLOOKUP on different search keys example
說明：

使用 VLOOKUP 時，你可以設定不同的搜尋值，例如「蘋果」和「橘子」。

如要避免傳回錯誤值，這些搜尋值必須位於指定範圍的第一欄。除了直接輸入搜尋值外，你也可以使用儲存格參照，例如「G9」。
搜尋值是「Orange」時
=VLOOKUP("Orange", B4:D8, 3, FALSE)
傳回值 = $1.01
搜尋值是「Apple」時
=VLOOKUP("Apple", B4:D8, 3, FALSE)
傳回值 = $1.50
搜尋值是設為「Apple」的儲存格參照 G9 時
=VLOOKUP(G9, B4:D8, 3, FALSE)
傳回值 = $1.50

使用 VLOOKUP 時設定不同的資料欄索引

使用 VLOOKUP 查找位於指定範圍第二欄中的橘子數量。
VLOOKUP on different column indexes example
說明：
使用 VLOOKUP 時，請設想範圍中的資料欄是由左至右從 1 開始編號。如要查找目標資訊，你必須指定其資料欄索引。例如，數量資料位於第 2 欄。
索引 = 2
查找橘子的數量，該資料位於範圍的第二欄。
=VLOOKUP(G3, B4:D8, 2, FALSE)
傳回值 = 5

VLOOKUP 完全比對或近似比對

  • 使用 VLOOKUP 完全比對方法查找確切的 ID。
  • 使用 VLOOKUP 近似比對方法查找近似的 ID。
VLOOKUP exact match or approximate match example
說明：
如要搜尋最接近的值 (不完全相符)，請採用近似比對方法 (已排序 = TRUE)。
如要搜尋資料表中不存在的 ID 值「102」，則近似比對方法會傳回較小的近似值「101」，因為在搜尋值資料欄中找到的「101」是小於且最接近「102」的值。
採用近似比對方法時，系統會沿著搜尋值資料欄向下搜尋，直到找到比指定搜尋值還大的值為止。系統會停在該較大近似值的前一個資料列，然後傳回該資料列位於傳回值資料欄中的值。換句話說，如果未依遞增順序排序搜尋值資料欄，則很可能會傳回不正確的值。
重要事項：採用近似比對方法前，請先依遞增順序排序搜尋值資料欄，確保傳回正確的值。否則，系統可能會傳回非預期值。
採用完全比對方法時 (例如，已排序 = FALSE)，系統會傳回完全相符的值。舉例來說，ID = 103 的水果名稱是「香蕉」(Banana)。如果找不到完全相符的值，系統就會顯示 #N/A 錯誤。我們建議使用完全比對方法，因為比較能夠預測其行為。
完全比對
=VLOOKUP(G6, A4:D8, 2, FALSE)
傳回值 =「Apple」
近似比對
=VLOOKUP(G3, A4:D8, 2, TRUE)
或
=VLOOKUP(G3, A4:D8, 2)
傳回值 =「Banana」

常見的 VLOOKUP 應用案例

替換 VLOOKUP 傳回的錯誤值

你可能會想要替換 VLOOKUP 在找不到搜尋值時所傳回的錯誤值。在這種情況下，如果不想傳回 #N/A，則可以透過 IFNA() 函式來替換 #N/A。請參閱這篇文章，進一步瞭解 IFNA()。
Replace error value from VLOOKUP example
「Fruit」欄中沒有「Pencil」這個搜尋值，因此 VLOOKUP 原本會傳回 #N/A。
IFNA() 會以函式中指定的第二個引數值取代 #N/A 錯誤值。在本範例中，指定的第二個引數值是「NOT FOUND」。
=IFNA(VLOOKUP(G3, B4:D8, 3, FALSE),"NOT FOUND")
傳回值 =「NOT FOUND」

提示：如要替換 #REF! 等其他錯誤值，請參閱這篇文章，進一步瞭解 IFERROR()。

指定多個條件的 VLOOKUP

VLOOKUP 無法直接套用多個條件。因此，請改為合併多個現有資料欄的內容來新增一行輔助資料欄，讓 VLOOKUP 能夠直接套用多個條件。
VLOOKUP with multiple criteria example
1. 使用「&」合併名字和姓氏來建立一行輔助資料欄。 =C4&D4，然後將它從 B4 向下拖曳到 B8，即可新增一行輔助資料欄。
2. 使用儲存格參照 B7 (JohnLee) 做為搜尋值。
=VLOOKUP(B7, B4:E8, 4, FALSE)
傳回值 =「Support」

指定萬用字元或部分比對的 VLOOKUP

你也可以在 VLOOKUP 中使用萬用字元或部分比對，可用的萬用字元如下：
  • 問號「?」可用於比對任何單一字元。
  • 星號「*」可用於比對任何字元序列。
如要在 VLOOKUP 中使用萬用字元，你必須指定完全比對方法 (已排序 = FALSE)。
VLOOKUP with wildcard example
「St*」是用來比對開頭為「St」的任何值，不受字元數的限制，例如「Steve」、「St1」、「Stock」或「Steeeeeeve」都是相符項目。
=VLOOKUP("St*", B4:D8, 3, FALSE)
傳回值 =「Marketing」

排解錯誤和最佳做法：

不正確的傳回值

  • 傳回非預期的值：如果你原本將已排序引數設為 TRUE，但範圍中的第一欄並未依遞增順序排序數字或字母，那麼請將「已排序」引數變更為 FALSE。

  • VLOOKUP 傳回第一個相符值：VLOOKUP 只會傳回第一個相符值。如果範圍中有多個與搜尋值相符的值，則只會傳回一個，而這個傳回值可能不是你預期的值。
  • 含有空格的資料：有時候，你可能會覺得資料結尾或開頭有空格沒什麼差別，但對 VLOOKUP 來說這些都算是不同的值。舉例來說，VLOOKUP 會視下列項目為不同的值：
    • " Apple"
    • "Apple "
    • "Apple"
如要得到預期的結果，使用 VLOOKUP 前請先移除空格。
詳情請參閱本文的最佳做法一節。
#N/A
  • 如果採用近似比對方法 (已排序 = TRUE)，且 VLOOKUP 的搜尋值小於第一欄中的最小值，則 VLOOKUP 會傳回 #N/A。
  • 如果採用完全比對方法 (已排序 = FALSE)，且 VLOOKUP 在第一欄中找不到與搜尋值完全相符的值，就會傳回 #N/A。在第一欄中找不到搜尋值時，如果不想傳回 #N/A，則可以透過 IFNA() 函式來替換 #N/A。
#REF!
若傳回這個錯誤，有可能是指定的範圍不正確，比實際範圍的最多欄數還多。如要避免發生這種情況，請務必檢查下列事項：
  • 計算欄數時，是計算所選範圍而非整個資料表的資料欄。
  • 要從 1 開始計算 (而非 0)。
#VALUE!
如果傳回 #VALUE! 錯誤，則可能的原因如下：
  • 將索引誤設為文字或欄名。
  • 輸入的索引數值小於 1。索引必須至少是 1，或小於等於範圍的最大欄數。VLOOKUP 只會查找搜尋值所在的資料欄 (如果索引 = 1)，或更右側的資料欄。

重要事項：索引的設定值必須是數字。

#NAME?
  • 如果你指定的搜尋值是文字，可能是沒有加上引號才傳回這個錯誤。
最佳做法

 

請這麼做 原因
使用絕對參照來指定範圍
最好這麼做：
  • 使用絕對參照來指定 VLOOKUP 的範圍
  • VLOOKUP(G3, $B$3:$D$7, 3, FALSE)
最好不要這麼做：
  • VLOOKUP(G3, B3:D7, 3, FALSE)
向下拖曳或複製範圍時，這個最佳做法可防止發生無法預測的變更。
採用近似比對方法時 (例如，已排序 = TRUE)，請依遞增順序排序第一個資料欄。 如果採用近似比對方法 (已排序 = TRUE)，則必須依遞增順序排序第一個資料欄。否則，很可能會傳回不正確的值。請參閱這篇文章，進一步瞭解如何排序。
使用 VLOOKUP 前先移除資料前後的空格
使用 VLOOKUP 前，務必移除資料前後的空格。若未移除空格，可能會造成 VLOOKUP 傳回無法預測的值。以下是一些常見的資料潛藏錯誤：
  • 開頭的空格：" apple"
  • 結尾的空格："apple "
  • 空白或空格："" 不等於 " "
如要去除開頭或結尾的空格，你可以使用「資料」下一步「資料清除」下一步「裁剪空格」功能。
不要將數字或日期值儲存為文字
確認 VLOOKUP 範圍第一欄 (搜尋值資料欄) 中的日期或數字資料不是儲存為文字格式。否則，系統可能會傳回非預期值。
  1. 在試算表中選取你的搜尋值資料欄。
  2. 依序輕觸頂端的「格式」選單 下一步「數值」。
  3. 依據你需要的資料類型，選擇下列任一選項：
    • 日期
    • 數字

還有其他問題嗎？

嘗試以下步驟：

張貼到產品討論社群 向社群成員尋求答案
true
造訪學習中心

您有在公司或學校使用 Google 文件等 Google 產品嗎？快來試試實用的秘訣、教學課程和範本，瞭解如何在不安裝 Office 的情況下處理 Office 檔案、建立動態專案計劃和小組日曆、自動整理收件匣及進行其他作業。

搜尋
清除搜尋內容
關閉搜尋
主選單
16827555205636160292
true
搜尋說明中心
true
true
true
true
true
35
false
false