How to Copy Source Formatting When Using VLOOKUP in Excel?

Sharing is caring!

When using VLOOKUP in Excel, one common limitation is its inability to retain the source formatting of the data it retrieves. This can be problematic if you want to maintain the same visual style for your lookup results. While Excel doesn’t support this feature natively, you can achieve it by combining Excel functions and VBA (Visual Basic for Applications).

In this article, we will guide you step-by-step on how to copy source formatting when using VLOOKUP in Excel. With these methods, you’ll be able to ensure your lookup results are visually consistent with your original data.

Why VLOOKUP Does Not Retain Formatting

By design, VLOOKUP is a function that retrieves data but does not carry over formatting such as font color, boldness, or cell background color. This is because Excel formulas only process the value of the data, not its appearance.

If you’re dealing with datasets where visual cues are important, this limitation can be frustrating. To copy formatting, you need to use additional tools like VBA or conditional formatting.

Using VBA to Copy Formatting When using VLOOKUP

To copy source formatting alongside the value retrieved by VLOOKUP, you can use a custom VBA function. This approach allows you to automate the process and handle large datasets efficiently. Below are the steps to implement this solution.

Step 1: Open the VBA Editor

1. Open your Excel workbook.
2. Right-click on the sheet tab where you want to apply this functionality.
3. From the context menu, select View Code. This will open the VBA Editor.

Step 2: Insert a Module

1. In the VBA editor, go to Insert > Module.
2. A new module will appear where you can write your custom code.

Step 3: Write the VBA Code

Use the following VBA code to create a function that performs VLOOKUP while copying the source formatting:

Function LookupKeepFormat(lookup_value As Variant, table_array As Range, col_index_num As Integer) As Variant
    Dim result As Variant
    Dim sourceCell As Range

    ' Perform the VLOOKUP
    On Error Resume Next
    result = Application.VLookup(lookup_value, table_array, col_index_num, False)
    On Error GoTo 0

    ' If found, copy formatting from the source cell
    If Not IsError(result) Then
        Set sourceCell = table_array.Columns(1).Find(lookup_value)
        If Not sourceCell Is Nothing Then
            Application.ScreenUpdating = False
            With Application.Caller.Offset(0, 0)
                .Value = result
                .Interior.Color = sourceCell.Interior.Color
                .Font.Bold = sourceCell.Font.Bold
                .Font.Color = sourceCell.Font.Color
            End With
            Application.ScreenUpdating = True
        End If
    End If

    LookupKeepFormat = result
End Function

Step 4: Use the Custom Function

1. Close the VBA editor and return to your Excel worksheet.
2. Use the new function like this:

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

• E2: The lookup value.
• $A$1:$C$8: The data range.
• 3: The column index from which to retrieve data.

1. Press Enter to see the result. This formula retrieves the value and applies the corresponding formatting from the source cell.

Step 5: Fill Down

You can drag the fill handle from the corner of the cell to apply the function to other rows. This ensures that all lookup results inherit the correct formatting from their corresponding source cells.

Example: Using VBA to Retain Formatting while doing VLOOKUP

Here’s an example to illustrate the process:

When you use the LookupKeepFormat function, the formatting from ProductA in the data range will be applied to the lookup result. This example demonstrates how both value and appearance can align seamlessly.

Using Conditional Formatting to Copy Source Formatting When Using VLOOKUP

If you don’t want to use VBA, you can replicate formatting visually using conditional formatting. Although it doesn’t copy the formatting directly, it allows you to highlight lookup results based on specific conditions.

This method works well for users unfamiliar with VBA but still looking to enhance their Excel sheets visually.

1. Select the range where you want to apply formatting.
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:

   =$E$2=$A1

5. Click Format and define the desired formatting (e.g., background color or bold text).
6. Click OK to apply.

While this method doesn’t directly copy the source cell’s formatting, it achieves a similar visual effect. You can define multiple rules to accommodate various conditions, making this approach highly customizable.

Limitations of Copying Formatting with VLOOKUP

Using VBA

• Basic Formatting Only: The VBA solution can copy basic formatting like font color, boldness, and background color but cannot handle advanced formatting (e.g., borders or conditional styles).
• Recalculation: If data changes in the lookup range, you may need to manually recalculate or refresh the formula to update formatting.
• Complexity: For users unfamiliar with VBA, setting up and maintaining these functions may seem daunting.

Using Conditional Formatting

• Predefined Rules: You need to set up formatting rules manually, which may not dynamically adapt to source cell styles.
• No Direct Formatting: This method only simulates formatting based on conditions and doesn’t pull the exact style from source cells.

VBA vs. Conditional Formatting: A Comparison

This table highlights the strengths and weaknesses of each approach, helping you decide which one aligns with your specific requirements.

Final Thoughts

While VLOOKUP doesn’t natively support formatting, the combination of VBA and Excel functions provides an effective workaround for copying basic formatting. For users who prefer not to use VBA, conditional formatting offers a simpler alternative to visually enhance lookup results.

Choosing the right method depends on your specific needs and level of familiarity with Excel tools. If you frequently deal with data requiring consistent formatting, investing time in learning VBA could significantly streamline your workflow.

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.