VLOOKUP

 

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

適用於 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 時，你可以設定不同的搜尋值，例如「蘋果」和「橘子」。

如要避免傳回錯誤值，這些搜尋值必須位於指定範圍的第一欄。除了直接輸入搜尋值外，你也可以使用儲存格參照，例如「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 時，請設想範圍中的資料欄是由左至右從 1 開始編號。如要查找目標資訊，你必須指定其資料欄索引。例如，數量資料位於第 2 欄。

索引 = 2 查找橘子的數量，該資料位於範圍的第二欄。

=VLOOKUP(G3, B4:D8, 2, FALSE) 傳回值 = 5

VLOOKUP 完全比對或近似比對

• 使用 VLOOKUP 完全比對方法查找確切的 ID。
• 使用 VLOOKUP 近似比對方法查找近似的 ID。

範例演練

說明：

如要搜尋最接近的值 (不完全相符)，請採用近似比對方法 (已排序 = 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()。

範例演練

「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 能夠直接套用多個條件。

範例演練

1. 使用「&」合併名字和姓氏來建立一行輔助資料欄。	=C4&D4，然後將它從 B4 向下拖曳到 B8，即可新增一行輔助資料欄。
2. 使用儲存格參照 B7 (JohnLee) 做為搜尋值。	=VLOOKUP(B7, B4:E8, 4, FALSE) 傳回值 =「Support」

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

你也可以在 VLOOKUP 中使用萬用字元或部分比對，可用的萬用字元如下：

• 問號「?」可用於比對任何單一字元。
• 星號「*」可用於比對任何字元序列。

如要在 VLOOKUP 中使用萬用字元，你必須指定完全比對方法 (已排序 = FALSE)。

範例演練

「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 範圍第一欄 (搜尋值資料欄) 中的日期或數字資料不是儲存為文字格式。否則，系統可能會傳回非預期值。 在試算表中選取你的搜尋值資料欄。 依序輕觸頂端的「格式」選單 「數值」。 依據你需要的資料類型，選擇下列任一選項： 日期 數字