How to Copy Source Formatting When Using VLOOKUP in Excel?

Sharing is caring!

When using VLOOKUP in Excel, it’s common to want the formatting of the source data to be copied over to the destination. By default, VLOOKUP only retrieves the values, not the formatting. However, there are a few workarounds to help you achieve this. In this article, you will learn how to copy the source formatting when performing a VLOOKUP and various methods that allow you to apply source formatting.

What is VLOOKUP in Excel?

VLOOKUP is one of the most widely used functions in Excel. It stands for “Vertical Lookup” and is used to search for a value in the leftmost column of a table and return a corresponding value in the same row from another column.

Syntax of VLOOKUP

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: The value you are searching for.
• table_array: The range of data where you want to search.
• col_index_num: The column number in the table from which to retrieve the data.
• range_lookup: A logical value that specifies whether you want an exact match (FALSE) or an approximate match (TRUE).

Importance of Formatting in Excel

Formatting in Excel enhances readability and helps make sense of the data quickly. Sometimes, you may want to retain the source formatting when pulling data using functions like VLOOKUP. Unfortunately, the VLOOKUP function by itself cannot carry over the formatting (such as font style, color, or borders) of the source cell.

Methods to Copy Source Formatting When Using VLOOKUP

Though VLOOKUP does not support copying formatting directly, several techniques can help you replicate the source formatting along with the data. Below are detailed methods on how to achieve this.

Method 1: Using Conditional Formatting

Conditional Formatting allows you to format cells based on specific conditions. You can use this technique to mimic the source formatting in the cells that return values from VLOOKUP.

Steps:

1. Use VLOOKUP to fetch the required data.
2. Apply Conditional Formatting to the cells that should reflect the formatting of the source.
3. Set up a conditional rule to format the destination cells based on the formatting in the source cells.

Example:

Assume you have a VLOOKUP formula in cell D2. You want to apply the same fill color as in the source cell A2.

• Highlight the cells where the VLOOKUP results are located.
• Navigate to the Home tab > Conditional Formatting > New Rule.
• Select Use a formula to determine which cells to format.
• Enter a formula that compares the cells in the lookup table with the results. For instance, =A2=D2.
• Choose the desired formatting, such as fill color, font style, or borders.

This method will make the destination cells match the source formatting based on their content.

Method 2: Using Copy-Paste Special (Formats)

This method requires manually copying the source formatting and pasting it into the destination cells where you used the VLOOKUP.

Steps:

1. Use VLOOKUP to get the desired data in the destination cells.
2. Select the source cells with the formatting you want to copy.
3. Right-click and choose Copy.
4. Highlight the VLOOKUP result cells.
5. Right-click and select Paste Special.
6. Choose Formats from the Paste Special options.

This will apply the formatting from the source cells to the destination cells without changing the VLOOKUP values.

Method 3: Using VBA Code

For a more automated and dynamic solution, you can use a VBA (Visual Basic for Applications) script to copy the source formatting along with the data retrieved using VLOOKUP.

Example VBA Code:

Sub CopySourceFormatting()
    Dim srcRange As Range
    Dim destRange As Range
    Dim cell As Range

    ' Define source and destination ranges
    Set srcRange = Range("A2:A10") ' Source range
    Set destRange = Range("D2:D10") ' Destination range with VLOOKUP results

    ' Loop through each cell in the source range
    For Each cell In srcRange
        ' Copy the formatting from the source to the destination
        cell.Copy
        destRange(cell.Row - srcRange.Row + 1).PasteSpecial Paste:=xlPasteFormats
    Next cell
End Sub

This code loops through the source cells and applies their formatting to the corresponding VLOOKUP result cells in the destination range.

Method 4: Using Get & Transform (Power Query)

If you are working with large datasets and need more flexibility in applying formatting, Power Query (also known as Get & Transform) can be a powerful tool. While Power Query does not handle formatting in the traditional sense, it allows you to manage data with more precision, enabling formatting rules to be applied later in the Excel environment.

Steps:

1. Use Power Query to load the data from your source and destination.
2. Perform the necessary transformations and load the data back into Excel.
3. Once the data is back in Excel, apply formatting rules or conditional formatting based on your needs.

Method 5: Using INDEX and MATCH with Formatting

The INDEX and MATCH combination can be an alternative to VLOOKUP if you’re looking for more flexibility in formatting and data retrieval.

Example:

=INDEX(A2:A10, MATCH(D2, B2:B10, 0))

Once you retrieve the data using INDEX and MATCH, apply formatting manually or with Conditional Formatting, as mentioned earlier.

Key Points to Remember

• VLOOKUP does not carry over formatting on its own.
• Conditional Formatting can help you automatically apply formatting based on rules.
• The Paste Special method is ideal for manual formatting but is not dynamic.
• VBA scripting allows automation of copying formatting, making it ideal for recurring tasks.
• Power Query and INDEX-MATCH can be helpful when working with larger datasets and complex needs.

Comparison of Methods to Copy Source Formatting

Tips to Improve Efficiency While Using VLOOKUP

1. Use templates: Set up a template where formatting is predefined. This will save time when applying the same formatting to VLOOKUP results.
2. VBA Macros: If you frequently use VLOOKUP with specific formatting requirements, consider creating a VBA Macro to automate the process.
3. Cell Styles: Utilize predefined Cell Styles in Excel to quickly apply consistent formatting across your workbook.

Limitations of Copying Source Formatting When Using VLOOKUP

• VLOOKUP is not designed to handle formatting. Therefore, copying source formatting will always require extra steps beyond the standard formula.
• Formatting rules in Excel can sometimes be lost when exporting to other formats, such as CSV or Text files.
• Large datasets may require more advanced methods such as Power Query or VBA for efficient formatting replication.

Final Thoughts

Copying source formatting when using VLOOKUP in Excel requires extra steps since the function is limited to data retrieval. However, with methods like Conditional Formatting, Paste Special, VBA, and others, you can effectively replicate formatting in your destination cells. Depending on your workflow, you can choose the method that suits your needs—whether you need quick manual formatting or an automated process for larger datasets.

By understanding these methods, you can improve the readability and presentation of your data while maintaining the convenience of VLOOKUP for data lookup tasks.

FAQs

Can VLOOKUP copy formatting from the source cell?

No, VLOOKUP only retrieves the value, not the formatting. However, you can use methods like Conditional Formatting, Paste Special, or VBA to replicate the formatting.

How do I use Conditional Formatting with VLOOKUP?

To use Conditional Formatting with VLOOKUP, set up a rule that formats the destination cells based on the values retrieved by VLOOKUP, ensuring it matches the source formatting.

Can I automate formatting when using VLOOKUP?

Yes, you can use a VBA script to automate copying formatting from the source cells to the destination cells where you’ve applied the VLOOKUP function.

What is the Paste Special method for copying formatting?

The Paste Special method allows you to manually copy formatting from source cells and paste it into the cells where VLOOKUP results are displayed without altering the data.

Can Power Query handle formatting with VLOOKUP?

Power Query does not handle formatting, but it can manage data efficiently. Once the data is loaded into Excel, you can apply formatting manually or with Conditional Formatting.

What is the best method for copying formatting with VLOOKUP for large datasets?

For large datasets, using VBA or Power Query combined with Conditional Formatting can help efficiently manage data and apply consistent formatting across your workbook.

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.