幫助中心 分析您的結果 資料匯出與分析 Google Sheets VLOOKUP 函數教學

Google Sheets VLOOKUP 函數教學

本教學說明如何在 Google 試算表（Google Sheets） 中使用 VLOOKUP 做資料比對，並介紹 SurveyMars 內建的 VLOOKUP 問卷關聯 題型如何實現問卷資料聯動。


一、認識 VLOOKUP

若您在試算表中已有部分已知資訊，可以使用 VLOOKUP 依列搜尋相關資料。例如：想買一顆橙子，可在價格表中用 VLOOKUP 根據「橙子」查其價格。


VLOOKUP（Vertical Lookup，垂直查詢）在指定區域第一欄中搜尋關鍵字，找到符合列後傳回該列由左數第 N 欄的值。適用於依編號、訂單號、手機號、工號等比對名稱、價格、狀態等欄位。


提示：查詢鍵值欄必須位於 range 的最左側；VLOOKUP 只能由左往右取欄，不能從右側欄反查左側欄。


函數語法


=VLOOKUP(查詢值, 查詢範圍, 傳回欄號, [是否近似比對])
    =VLOOKUP(search_key, range, index, [is_sorted])

search_key（查詢值）：要搜尋的值，可為儲存格參照或具體文字/數字；須位於 range 第一欄。

range（查詢範圍）：包含鍵值欄與傳回欄的資料區域；可使用命名範圍，跨工作表寫作 Sheet2!$A$2:$B$100。

index（傳回欄號）：從 range 最左欄起算，從 1 開始的正整數。

is_sorted（是否近似比對，選用）：FALSE 完全比對（建議）；TRUE 近似比對（使用前須對第一欄遞增排序）。省略時預設為 TRUE。



二、VLOOKUP 函數範例

範例 1：在 G9 輸入水果名稱，用下列公式在 B4:D8 中查價格（第 3 欄為價格）：

=VLOOKUP(G9, B4:D8, 3, FALSE)
    =VLOOKUP("Apple", B4:D8, 3, TRUE)

VLOOKUP 範例


以下範例整理自 Google 官方說明。點擊標題可展開/收合；各範例可點擊「試試看」在試算表副本中練習。


VLOOKUP 基本範例

使用 VLOOKUP 查詢不同的搜尋鍵值

使用 VLOOKUP 查詢「Orange」和「Apple」的價格。

VLOOKUP 不同搜尋鍵值範例

試試看


說明：可使用不同搜尋鍵值（如「Apple」「Orange」）。若要傳回非錯誤值，這些搜尋鍵值必須位於 range（範圍）的第一欄中。若不想為搜尋鍵值填入值，也可使用儲存格參照，例如「G9」。

說明公式與傳回值
search_key （搜尋鍵值）為「Orange」=VLOOKUP("Orange", B4:D8, 3, FALSE)
傳回值 = $1.01
search_key （搜尋鍵值）為「Apple」=VLOOKUP("Apple", B4:D8, 3, FALSE)
傳回值 = $1.50
search_key （搜尋鍵值）使用「Apple」的儲存格參照（G9）=VLOOKUP(G9, B4:D8, 3, FALSE)
傳回值 = $1.50
使用 VLOOKUP 查詢不同欄編號

在第二欄（數量）中查詢「Orange」的數量。

VLOOKUP 不同欄編號範例

試試看


說明：假設 range（範圍）中的欄由左至右編號，且從 1 開始。若要查詢目標資訊，您必須指定其欄編號。例如，第 2 欄表示數量。

說明公式與傳回值
index （編號）為 2
找出橙子的數量，也就是 range（範圍）的第二欄。		=VLOOKUP(G3, B4:D8, 2, FALSE)
傳回值 = 5
VLOOKUP 完全比對或近似比對

- 完全比對（FALSE）：查詢確切 ID。
- 近似比對（TRUE）：查詢最接近的 ID（第一欄須遞增排序）。


VLOOKUP 完全比對與近似比對範例

試試看


說明：搜尋 ID 102 但表中無此值時，近似比對可能傳回 101（小於 102 的最大值）。若第一欄未遞增排序，結果可能錯誤。日常情境建議使用完全比對。

重要提示：使用近似比對前，須對搜尋鍵值欄遞增排序。

比對方式公式與傳回值
完全比對=VLOOKUP(G6, A4:D8, 2, FALSE)
傳回值 =「Apple」
近似比對=VLOOKUP(G3, A4:D8, 2, TRUE) 或省略第四參數
傳回值 =「Banana」

VLOOKUP 常見應用情境

取代 VLOOKUP 中的錯誤值

搜尋鍵值不存在時，可用 IFNA() 將 #N/A 取代為自訂文案。

用 IFNA 取代 VLOOKUP 錯誤值範例

試試看

說明公式與傳回值
一開始，VLOOKUP 會傳回「#N/A」，因為「Fruit」欄中不存在搜尋鍵值「Pencil」。
IFNA() 會將「#N/A」錯誤取代為函數中指定的第二個輸入項。在我們的範例中，其為「NOT FOUND」。		=IFNA(VLOOKUP(G3, B4:D8, 3, FALSE), "NOT FOUND")
傳回值 =「NOT FOUND」

提示：取代 #REF! 等其他錯誤可使用 IFERROR()。

在 VLOOKUP 中使用多個條件

VLOOKUP 無法直接以多條件查詢。可建立輔助欄，用 & 合併多欄作為鍵值。

VLOOKUP 多條件輔助欄範例

試試看

步驟公式與傳回值
1. 若您使用「&」將「First Name」和「Last Name」合併，則可以建立「Helper」欄。=C4&D4，並將其從 B4 向下拖曳到 B8，即可得到「Helper」欄。
2. 將儲存格參照 B7 (JohnLee) 用作搜尋鍵值。=VLOOKUP(B7, B4:E8, 4, FALSE)
傳回值 =「Support」
在 VLOOKUP 中使用萬用字元或部分比對項

? 比對單一字元，* 比對任意序列。須配合 FALSE（完全比對）使用。

VLOOKUP 萬用字元範例

試試看

說明公式與傳回值
"St*" 比對以 St 開頭的名稱，例如「Steve」「St1」「Stock」或「Steeeeeeve」。=VLOOKUP("St*", B4:D8, 3, FALSE)
傳回值 =「Marketing」


三、出錯了怎麼排查

依下面情境對照即可；多數問題出在「鍵值欄位置、格式、是否完全比對、範圍是否鎖定」四件事上。


1. 表裡明明有這筆資料，為什麼還是 #N/A？

答：依序檢查——①查詢值是否在 range 的最左欄；②文字與數字是否一致（如 001 與 1）；③首尾是否有多餘空格；④資料來源裡是否真有該值。可用 TRIM 去空格，用 VALUE 或 TEXT 統一格式後再查。


2. 公式突然變成 #REF!，是欄號寫錯了嗎？

答：通常是 index（傳回欄號）大於 range 實際欄數。從 range 最左欄重新數到目標欄，修正欄號即可。


3. 提示 #VALUE!，公式哪裡不對？

答：常見原因：index 小於 1；第四參數不是 TRUE/FALSE；range 參照無效。建議 index ≥ 1，第四參數寫 FALSE（完全比對）。


4. 能算出結果，但跟試算表裡的值對不上？

答：檢查是否誤用 TRUE（近似比對）——日常請改用 FALSE；range 第一欄若有重複鍵，VLOOKUP 只傳回第一筆；若多列結果雷同，多半是拖曳時 range 沒鎖定。


5. 往下複製公式後，只有第一列對、下面全錯？

答：查詢範圍要用絕對參照鎖定，如 $B$4:$D$100；跨工作表寫作 Sheet2!$A$2:$B$500，避免拖曳時區域跟著偏移。


6. 想根據「姓名」查「工號」，VLOOKUP 為什麼不行？

答：VLOOKUP 只能由左往右查，鍵值欄必須在 range 最左側。可把工號欄移到左邊，或改用 INDEX + MATCH、XLOOKUP。若公式報錯，確認您所在地區的分隔符號是逗號還是分號（部分地區為 ;）。


四、SurveyMars：用 VLOOKUP 問卷關聯實現問卷資料聯動

若需在問卷作答時根據歷史問卷回覆或檔案自動帶出欄位，不必匯出到試算表再寫 VLOOKUP。SurveyMars 的 VLOOKUP 問卷關聯 題型可在平台內完成問卷資料聯動：填答者輸入比對關鍵字後，系統從來源問卷查詢並自動回填已設定題目。


與 VLOOKUP 的對應關係


VLOOKUP 概念SurveyMars 設定
range（查詢範圍）來源問卷資料
search_key（查詢值）填答者填寫的比對項
index（傳回欄）待自動回填的題目欄位


適用情境

- 會員/員工檔案已蒐集，新調研用少量關鍵字帶出基礎資訊

- 活動報名核對與資訊補全

- 多輪調研與歷史問卷回覆欄位對齊


設定說明

- 視覺化設定：編輯端勾選來源問卷與回填欄位，無需手寫公式

- 作答即聯動：提交前完成比對與回填，減少匯出二次處理

- 權限可控：資料留在問卷系統內，便於管理存取範圍


如何精準設定來源問卷、比對項與回填欄位？解鎖 SurveyMars VLOOKUP 問卷資料關聯方法

查看 VLOOKUP 問卷關聯官方說明


常見問題

問：手機號、工號看起來一模一樣，為什麼就是比對不上？


答：多半是「看起來一樣、實際不一樣」——一欄是文字、一欄是數字，或首尾有空格、前導零被吃掉。可先用 =TRIM(A1) 比對兩格是否真相等；需要統一格式時配合 VALUE 或 TEXT。仍顯示 #N/A 時，再依第三節逐項排查。


問：比對不到時不想滿版 #N/A，有沒有更友善的顯示？


答：可用 IFNA 包一層，找不到時顯示「找不到」等自訂文案，例如 =IFNA(VLOOKUP(G3, B4:D8, 3, FALSE), "找不到")。第二節「取代 VLOOKUP 中的錯誤值」有完整範例。


問：價格表在另一個工作表裡，公式該怎麼寫？


答：在 range 前加工作表名稱，如 =VLOOKUP(G9, 價格表!$A$2:$D$200, 3, FALSE)。工作表名稱含空格時用單引號：'2024 價格'!$A$2:$D$200。範圍建議加 $ 絕對參照，避免複製公式時偏移。


問：我有兩份名單要對齊，VLOOKUP 和 INDEX+MATCH 該用哪個？


答：鍵值在查詢區域最左欄、只需往右取欄時，VLOOKUP 就夠用。若要依右側欄（如姓名）反查左側（如工號），或傳回欄在鍵值欄左邊，請用 INDEX + MATCH 或 XLOOKUP。Google 試算表已支援 XLOOKUP，語法更直覺。


問：問卷裡想讓填答者輸入手機號就自動帶出姓名，還要匯出 Excel 寫 VLOOKUP 嗎？


答：不必。在 SurveyMars 中新增 VLOOKUP 問卷關聯 題型，設定來源問卷與回填欄位即可在作答時完成聯動，無需先匯出再寫公式。設定步驟見第四節，或點擊上方綠色按鈕查看官方說明。


這篇文章有多大幫助？
上一篇
新增答卷提醒
滿意度報告分析和洞察
下一篇