如何在 Excel 中對以文字格式儲存的數字執行 VLOOKUP？

作者曉陽修改日期2025-07-23

在 Excel 中使用 VLOOKUP 時，若查閱值為文字格式，而查閱欄位卻是數字格式（或反之），便會因格式不一致導致查閱失敗或傳回錯誤結果。這類問題相當常見，尤其在處理來自外部來源、匯入資料，或多人協作的大型資料集時更易發生。解決這些格式差異，對於確保 VLOOKUP 正確運作並取得準確資訊至關重要。本逐步指南將介紹多種實用解決方案，協助您有效處理格式不一致的狀況，無論工作簿中的數字以何種形式儲存，都能確保查閱結果可靠且精準。

本文將示範處理此類錯誤的有效方法，包括調整公式、運用 Excel 內建工具，以及適用於大量或自動化處理的 VBA 技術。同時，我們也會解析每種方法的優勢與注意事項，協助您根據自身需求選擇最合適的解決方案。

使用公式對以文字格式儲存的數字執行 VLOOKUP
使用 Kutools for Excel 快速修正格式不一致問題
VBA 巨集：在 VLOOKUP 前標準化格式
其他內建 Excel 方法：使用「文字分列」修正資料格式

顯示在 Excel 中因數字格式不相符而使用 VLOOKUP 時出現錯誤的螢幕截圖

使用公式對以文字格式儲存的數字執行 VLOOKUP

若您的查閱資料中，部分數字以文字格式儲存，另一部分則為實際數值，VLOOKUP 便會因格式不一致而無法找到相符項目。其中最直接的解決方案之一，是在執行查閱時，透過 Excel 公式即時將查閱值或查閱欄位轉換為統一格式。此方法適用於大多數工作表操作，不僅易於套用，還不會變更原始資料。

例如，若您的查閱值以文字格式儲存，而表格中的對應欄位為數字格式，即可在 VLOOKUP 公式中使用 VALUE 函數，將文字轉換為數字。

在欲顯示結果的空白儲存格中輸入下列公式：

=VLOOKUP(VALUE(G1),A2:D15,2,FALSE)

輸入公式後，按下 Enter 鍵，即可根據條件取得對應值，如下圖所示：

顯示透過 VALUE 公式統一數字格式後，VLOOKUP 正確運作的螢幕截圖

參數說明與提示：

G1：包含您欲查閱之值的儲存格（可為文字或數字）。
A2:D15：包含查閱欄位與您要傳回資訊之欄位的資料表範圍。
2：您要傳回結果的欄位編號（從表格範圍最左側欄位起算）。

請留意待檢索值區域中是否含有前導或尾隨空格，這些也可能導致查閱失敗。若資料可能包含多餘空格，建議搭配使用 TRIM 函數，輕鬆提升查找準確率！

若查閱值為真正的數字（數字格式），但表格中的對應欄位卻以文字儲存，則需在執行查閱前將數值轉換為文字。此時可使用 TEXT 函數：

=VLOOKUP(TEXT(G1,0),A2:D15,2,FALSE)

在目標儲存格中輸入此公式，按下 Enter，即可取得如下所示的正確結果：

顯示透過 TEXT 公式統一數字格式後，VLOOKUP 正確運作的螢幕截圖

此處 TEXT 函數中的數字格式代碼「0」可確保您的數字在比對前先轉換為純文字值。

若您不確定待檢索值區域可能採用的格式，或預期查閱欄中同時存在拆分為文本和數字，可利用 IFERROR 函數嵌套兩種方法，無縫處理所有可能性：

=IFERROR(VLOOKUP(VALUE(G1),A2:D15,2,0),VLOOKUP(TEXT(G1,0),A2:D15,2,0))

在結果儲存格中輸入此公式：它會先嘗試將您的值轉換為數字進行查閱；若轉換失敗（例如該值無法強制轉為數字），則自動改以文字格式再次查閱。此方法特別適用於格式混雜的資料集，或多人共用且資料輸入標準不一的檔案。

輸入上述任一公式後，若需套用至多個待檢索值區域，請將公式複製到相鄰儲存格——只需選取該儲存格並向下拖曳填滿控點，或視需要使用 Ctrl+C 與 Ctrl+V。針對大型表格，這些公式不僅能確保比對結果可靠，還不會變更原始資料庫，讓您安心高效處理資料！

此方法為大多數工作表查閱需求提供靈活且通用的解決方案。然而，若您處理的是極大型資料集，或需自動化處理大量記錄，則可考慮運用 VBA 等自動化工具，進一步提升效率。

使用 Kutools for Excel 快速修正格式不一致問題

若您偏好更快且無需公式的解決方案，Kutools for Excel 提供了一項名為「文字與數值之間的轉換」的使用者友善工具。只需輕點幾下，即可將以文字儲存的數字轉換為真正的數值（或反向轉換），特別適合在執行 VLOOKUP 或 MATCH 等查詢前快速修正格式問題。

安裝 Kutools for Excel 後，請依照下列步驟操作。

選取包含問題資料的範圍（例如以文字格式儲存的數字）。
前往「Kutools」>「內容」>「文字與數值轉換」。
在彈出的對話方塊中：
1. 若您因數字以文字格式儲存而導致查閱失敗，請選擇「文字至數值」。
  （若待檢索值區域為文字格式，請選擇「數值至文字」。）
2. 點擊「確定」，立即轉換資料格式。

轉換文字為數值後，已轉換的儲存格將被視為真正的數字，不再顯示代表格式不一致的綠色三角形標記。

此方法無需輔助欄、公式或 VBA，非常適合在使用 VLOOKUP 前快速清理資料。

Kutools for Excel－透過超過 300 項必備工具全面強化 Excel，讓您的工作更快速、更輕鬆，並運用 AI 功能實現更聰明的資料處理與高效生產力！立即取得

VBA 巨集：在 VLOOKUP 前標準化格式

對於經常處理大型資料集、接收外部來源檔案，或需要重複執行自動化作業的使用者來說，運用簡易的 VBA 巨集即可程式化地將查閱值欄位與查閱表格欄位的資料格式標準化。如此一來，您便能在執行 VLOOKUP 前，確保所有資料統一轉換為文字或數字格式，徹底杜絕因格式不一致所導致的比對錯誤。VBA 尤其適合批量處理，不僅大幅節省手動調整的時間，更能透過自動化確保資料的一致性。

優點：可自動化處理大範圍區域或重複性工作流程，有效降低遺漏與格式不一致的風險，特別適合執行重複性任務。
缺點：不適用於受巨集限制的使用者，或不熟悉 VBA 巨集操作的人士。

以下是使用巨集標準化單元格格式的方法：

1. 前往開發人員選項卡，點選 Visual Basic 以開啟 VBA 編輯器。在新視窗中，點選插入＞模組，並將下列程式碼複製貼上至模組區域：

Sub StandardizeLookupFormats()
    ' Ask the user to select the lookup column and choose a target format
    Dim rng As Range
    Dim userChoice As Integer
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.InputBox("Select the range to standardize (lookup or data column):", xTitleId, Type:=8)
    If rng Is Nothing Then Exit Sub
    
    userChoice = MsgBox("Convert selected data to Number? (Click Yes to convert to Number, No to convert to Text)", vbYesNoCancel, xTitleId)
    
    If userChoice = vbYes Then
        For Each cell In rng
            If IsNumeric(cell.Value) Then
                cell.Value = Val(cell.Value)
                cell.NumberFormat = "General"
            End If
        Next
    ElseIf userChoice = vbNo Then
        For Each cell In rng
            If Not IsEmpty(cell.Value) Then
                cell.Value = CStr(cell.Value)
                cell.NumberFormat = "@"
            End If
        Next
    Else
        Exit Sub
    End If
End Sub

2. 關閉 VBA 編輯器。若要執行巨集，請返回 Excel，按下 Alt+F8，選取 StandardizeLookupFormats，然後點擊執行。

操作詳情與提示：

此巨集將提示您選取欲標準化的欄位（可為查閱範圍或表格範圍）。
選取後，系統會詢問您是要將範圍轉換為數字（按一下「是」）還是文字（按一下「否」）。為確保 VLOOKUP 能可靠比對，請務必為查閱欄與表格欄選擇相同的格式。
執行此巨集後，若結果未立即顯示，您可能需要重新計算工作表（按下）F9）或重新套用 VLOOKUP 公式。
若您收到巨集已停用的錯誤訊息，請先至 Excel 設定中啟用巨集，再繼續操作。

此解決方案非常適合用於重複性資料匯入，或在對大型資料集套用 VLOOKUP 或其他查詢運算前，清理格式不一致的欄位。

其他內建 Excel 方法：使用「文字分列」修正資料格式

在 Excel 中快速統一數字與文字格式的方法，就是善用文字分列功能！這項內建工具雖常用於分割資料，卻也能在不更動公式的情況下強制轉換格式，特別適合一次性修正或處理簡易清單，效率大幅提升！

優點：操作極其簡單，無需公式或程式碼，還能完整保留原始資料結構；缺點：最適合一次性修正，若資料變動則無法自動更新。

若要使用此方法將儲存為文字的數字（或反之）轉換格式，請執行下列步驟：

選取格式可能不符的欄位（例如您用來查閱或作為 VLOOKUP 參照的欄位）。
在資料索引標籤上，點選文字分列。
在精靈中，選取分隔符號後，按一下下一步。
取消勾選所有分隔符號的核取方塊（因為您並非要拆分資料），然後按一下下一步。
在欄位資料格式中，選取一般（強制 Excel 將數字識別為數值）或選取文字（將數值轉換為文字）。
點擊完成，立即結束此程序。

完成後，您的資料格式將自動統一為數字或文字，徹底解決 VLOOKUP 比對失敗的問題。請務必檢查幾個儲存格，確認轉換結果符合預期。如有需要，請同時對查詢欄位與待檢索值區域執行相同操作，以確保最高程度的一致性。

實用提醒：「文字分列」會直接修改原始資料，若右側相鄰儲存格已有內容，可能會被覆寫。如不確定，建議先將欄位複製至空白區域，並在使用大量資料處理工具前務必備份檔案！

最佳 Office 生產力工具

🤖	KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多