How to Copy Source Formatting When Using VLOOKUP in Excel?

Sharing is caring!

When you use VLOOKUP in Excel, one common frustration is that it retrieves only the value from the source data, but not the formatting (like font color, boldness, or background color). If your dataset uses formatting as a visual cue, this can make your lookup results look inconsistent.

Unfortunately, Excel doesn’t support formatting retrieval natively through formulas. However, you can work around this limitation using VBA (Visual Basic for Applications) or Conditional Formatting. In this guide, we’ll walk through reliable methods to ensure your lookup results retain the desired formatting.

Why VLOOKUP Does Not Retain Formatting

By design, VLOOKUP and other Excel formulas only handle values. They don’t carry formatting information because Excel separates a cell’s content (value) from its appearance (formatting).

That means when you run a formula like:

=VLOOKUP(E2, $A$1:$C$8, 3, FALSE)

you’ll get the correct value but lose any special styling that was in the source cell.

If formatting consistency is critical, you’ll need to use VBA or simulate formatting with Conditional Formatting rules.

Method 1: Using VBA with a Worksheet Event (Recommended)

One common mistake is trying to make a UDF (user-defined function) change formatting directly inside the formula cell. This causes circular reference errors because Excel formulas aren’t allowed to alter formatting.

Instead, you should separate the tasks:

1. A formula (or UDF) retrieves the value.
2. A worksheet event or macro applies the formatting automatically.

Step 1: Create a Simple Lookup Function (Optional)

In a standard VBA module (Insert > Module), paste:

Public Function LookupKeepFormat(lookup_value As Variant, table_array As Range, col_index_num As Long) As Variant
    LookupKeepFormat = Application.VLookup(lookup_value, table_array, col_index_num, False)
End Function

This works like a normal VLOOKUP, but just returns the value.

Usage in Excel:

=LookupKeepFormat(E2, $A$1:$C$8, 3)

Step 2: Add a Worksheet Event to Copy Formatting

Right-click the sheet tab > View Code, then paste this event procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim watchCol As Range, c As Range
    Dim m As Variant, src As Range, outCell As Range
    Dim tbl As Range

    ' Adjust these ranges to fit your sheet
    Set watchCol = Me.Range("E2:E100")       ' lookup values
    Set tbl = Me.Range("$A$1:$C$8")          ' source table

    Dim hit As Range
    Set hit = Intersect(Target, watchCol)
    If hit Is Nothing Then Exit Sub

    On Error GoTo CleanExit
    Application.EnableEvents = False

    For Each c In hit.Cells
        If Len(c.Value) > 0 Then
            m = Application.Match(c.Value, tbl.Columns(1), 0)
            If Not IsError(m) Then
                Set outCell = c.Offset(0, 1) ' result column (adjust if needed)
                Set src = tbl.Columns(1).Cells(m, 1)

                outCell.ClearFormats
                src.Copy
                outCell.PasteSpecial xlPasteFormats
                Application.CutCopyMode = False
            End If
        End If
    Next c

CleanExit:
    Application.EnableEvents = True
End Sub

How it works:

• You enter a lookup value in column E.
• The formula in column F retrieves the value.
• The event automatically copies the formatting from the matched row’s column A into the result cell.

No circular references, no broken formulas.

Method 2: One-Click Macro for Bulk Operations

If you prefer to update values and formatting in one go (instead of dynamically), you can use a standalone macro. This is great for large datasets.

Paste this into a standard module:

Public Sub VLookupCopyFormat(lookupRange As Range, table_array As Range, col_index_num As Long, outputRange As Range)
    Dim i As Long, r As Variant
    Dim src As Range, outCell As Range

    If lookupRange.Rows.Count <> outputRange.Rows.Count Then
        MsgBox "lookupRange and outputRange must have the same number of rows.", vbExclamation
        Exit Sub
    End If

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False

    For i = 1 To lookupRange.Rows.Count
        r = Application.Match(lookupRange.Cells(i, 1).Value, table_array.Columns(1), 0)
        Set outCell = outputRange.Cells(i, 1)

        If Not IsError(r) Then
            outCell.Value = Application.Index(table_array.Columns(col_index_num), r)

            Set src = table_array.Columns(1).Cells(r, 1)
            outCell.ClearFormats
            src.Copy
            outCell.PasteSpecial xlPasteFormats
            Application.CutCopyMode = False
        Else
            outCell.Value = CVErr(xlErrNA)
            outCell.ClearFormats
        End If
    Next i

    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Example usage:

Sub RunIt()
    VLookupCopyFormat Range("E2:E100"), Range("$A$1:$C$8"), 3, Range("F2:F100")
End Sub

This fills column F with values and formatting in one shot.

Method 3: Using Conditional Formatting

If you don’t want to use VBA at all, you can simulate formatting with Conditional Formatting:

1. Select the output range.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter a formula such as: =$E2=$A$1
5. Define the formatting you want (bold, background color, etc.).
6. Add multiple rules for different conditions.

This won’t copy exact source formatting, but it gives you a flexible, no-code way to visually align your lookup results.

VBA vs. Conditional Formatting: A Quick Comparison

Final Thoughts

Excel formulas like VLOOKUP are limited to returning values only. If you need to carry over formatting, you have two practical options:

• VBA (event or macro) — best if you want exact formatting copied automatically.
• Conditional Formatting — best if you prefer a simpler, no-code approach.

If your workbooks depend on consistent visual styles, investing a little time in VBA can save you hours of manual formatting in the long run.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.