在 Excel 中使用 VLOOKUP 時，如何複製查找到的儲存格原始格式？

作者Siluvia修改日期2024-11-01

在先前的文章中，我們已探討過如何在 Excel 執行 VLOOKUP 時保留背景顏色。本文將進一步介紹一種方法，讓您在執行 VLOOKUP 的同時，完整複製結果儲存格的所有格式。請依照下列步驟操作。

在 Excel 中透過使用者自訂函數於 VLOOKUP 時複製原始格式

在 Excel 中透過使用者自訂函數於 VLOOKUP 時複製原始格式

假設您有一張如下方截圖所示的表格。現在，您需要檢查 E 欄中的特定值是否出現在 A 欄中，並從 C 欄返回對應的值及其格式。請依照下列步驟操作以達成此目的。

VLOOKUP 保留儲存格格式

1. 在包含您要執行 VLOOKUP 查找值的工作表中，以滑鼠右鍵點擊工作表標籤，然後從內容功能表中選取檢視程式碼。請參閱截圖：

在工作表標籤上按一下滑鼠右鍵，然後選擇「檢視程式碼」

2. 在開啟的 Microsoft Visual Basic for Applications 視窗中，請將下方 VBA 程式碼貼上至程式碼視窗。

VBA 程式碼 1：VLOOKUP 並返回值保留格式

Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20211203
    Dim I As Long
    Dim xKeys As Long
    Dim xDicStr As String
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    xKeys = UBound(xDic.Keys)
    If xKeys >= 0 Then
        For I = 0 To UBound(xDic.Keys)
            xDicStr = xDic.Items(I)
            If xDicStr <> "" Then
                Set xRg = Application.Range(xDicStr)
                xRg.Copy
                Range(xDic.Keys(I)).PasteSpecial xlPasteFormats
            Else
                Range(xDic.Keys(I)).Interior.Color = xlNone
            End If
        Next
        Set xDic = Nothing
    End If
    Application.ScreenUpdating = True
    Application.CutCopyMode = True
End Sub

3. 接著點擊插入> 模組，並將下方的 VBA 程式碼 2 複製到模組視窗中。

VBA 程式碼 2：VLOOKUP 並返回值保留格式

Public xDic As New Dictionary
'Update by Extendoffice 20211203
Function LookupKeepFormat(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
    Dim xFindCell As Range
    On Error Resume Next
    Application.ScreenUpdating = False
    Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
    If xFindCell Is Nothing Then
        LookupKeepFormat = " "
        xDic.Add Application.Caller.Address, " "
    Else
        LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
        xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address(External:=True)
    End If
    Application.ScreenUpdating = True
End Function

4. 點擊工具 > 參考設定，然後在 參考設定 – VBAProject對話方塊中，於 Microsoft Script Runtime 方塊前打勾。請參閱截圖：

工具 > 參考，然後勾選 Microsoft Script Runtime 方塊

5. 按下 Alt+Q 鍵，即可關閉 Microsoft Visual Basic for Applications 視窗。

6. 選取緊鄰查找值的空白儲存格，於編輯欄輸入公式 =LookupKeepFormat(E2,$A$1:$C$8,3)，再按下 Enter 鍵。

套用公式以取得第一筆結果

注意：公式中，E2 為您要查找的值，$A$1:$C$8 為表格範圍，數字 3 則代表要返回的對應值位於表格的第三欄。請依實際需求調整這些參數！

7. 保持選取第一個結果儲存格，並向下拖曳填滿控點，即可取得所有結果及其格式，如下方截圖所示。

拖曳並填滿公式至其他儲存格

透過 KUTOOLS AI 解鎖 Excel 的神奇功能

智慧執行：透過簡易指令，輕鬆執行儲存格操作、分析資料，並建立圖表！
自訂公式：打造專屬公式，讓您的工作流程更順暢！
VBA 編碼：輕鬆撰寫並套用 VBA 程式碼，立即提升工作效率！
公式解析：輕鬆掌握複雜公式！
文字翻譯：輕鬆打破試算表中的語言隔閡！

運用 AI 驅動的工具強化您的 Excel 能力。立即下載體驗前所未有的高效！

最佳 Office 生產力工具

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