Cookies help us deliver our services. By using our services, you agree to our use of cookies.
Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


How to copy source formatting of the lookup cell when using Vlookup in Excel?

In the previous articles, we have talked about keeping background color when vlookup values in Excel. Here in this article, we are going to introduce a method of copying all cell formatting of the resulting cell when doing Vlookup in Excel. Please do as follows.

Copy source formatting when using Vlookup in Excel with a User-defined function

Combine multiple worksheets/workbooks into one worksheet / workbook:

Combine multiple worksheets or workbooks into one single worksheet or workbook may be a huge task in your daily work. But, if you have Kutools for Excel, its powerful utility – Combine can help you quickly combine multiple worksheets, workbooks into one worksheet or workbook

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!

Copy source formatting when using Vlookup in Excel with a User-defined function

Supposing you have a table as below screenshot shown. Now you need to check if a specified value (in column E) is in column A and return corresponding value with formatting in column C. Please do as follows to achieve it.

1. In the worksheet contains the value you want to vlookup, right-click the sheet tab and select View Code from the context menu. See screenshot:

2. In the opening Microsoft Visual Basic for Applications window, please copy below VBA code into the Code window.

VBA code 1: Vlookup and return value with formatting

Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20180706
    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
                Range(xDic.Keys(I)).PasteSpecial xlPasteFormats
                Range(xDic.Keys(I)).Interior.Color = xlNone
            End If
        Set xDic = Nothing
    End If
    Application.ScreenUpdating = True
    Application.CutCopyMode = True
End Sub

3. Then click Insert > Module, and copy the below VBA code 2 into the Module window.

VBA code 2: Vlookup and return value with formatting

Public xDic As New Dictionary
'Update by Extendoffice 20180706
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, " "
        LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
        xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
    End If
    Application.ScreenUpdating = True
End Function

4. Click Tools > References. Then check the Microsoft Script Runtime box in the References – VBAProject dialog box. See screenshot:

5. Press the Alt + Q keys to exit the Microsoft Visual Basic for Applications window.

6. Select a blank cell adjacent to the lookup value, and then enter formula =LookupKeepFormat(E2,$A$1:$C$8,3) into the Formula Bar, and then press the Enter key.

Note: In the formula, E2 contains the value you will lookup, $A$1:$C$8 is the table range, and number 3 means that the corresponding value you will return locates in the third column of the table. Please change them as you need.

7. Keep selecting the first result cell, and then drag the Fill Handle down to get all results along with their formatting as below screenshot showed.

Related articles:

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Leigh · 4 days ago
    Hello, I've been using the above code in Excel 2010 with no problems to date. However, I was recently upgraded to Office 2016 and now the code crashes Excel every time I try to fill down more than one row. Unfortunately, it is not giving me an error other than "Microsoft Excel has stopped working". I was wondering if you have come across this issue previously, and if there is something I need to do to make it work in 2016. Thanks!
  • To post as a guest, your comment is unpublished.
    Laura · 3 months ago
    Hello. I created a blank spreadsheet and duplicated your example in Excel 2013, but keep getting a Compile error: Syntax error and Dim I As Long is highlighted. Is there something I'm missing? I would love to get this working. Thank you.
  • To post as a guest, your comment is unpublished.
    Jeni · 4 months ago
    I tried this one and the the one that pulls just the color background and am getting the same error. Compile error: Ambiguous name detected. I click OK and it highlights xDic. Any suggestions? I'm not super familiar with all of this so please help/explain :) thanks in advance
  • To post as a guest, your comment is unpublished.
    Heather M · 5 months ago
    Also, if I add your formula as part of an "If" statement (see below), it formats the cell however it wants LOL (or at least it seems so. One cell, the text went shadowed and bold with a top border on the cell; another cell, the text centered)

    =IF($F19 = "", "",LookupKeepFormat(F19,'Item #s'!$A$1:$M$1226,2))
  • To post as a guest, your comment is unpublished.
    Heather M · 5 months ago

    I get no errors and it does the lookup, but because my lookup value is on another worksheet (a more likely scenario), it doesn't pull the formatting. Is there a tweak to the code that I can make for that? (Be very specific as to where the change needs to go as I'm a coding novice) Thank you! I'm excited to add this feature to one of my spreadsheets!!
    • To post as a guest, your comment is unpublished.
      Chirag · 3 months ago
      Hi, any luck on this question, how can we get the formatting to be looked up across sheets?