Can Excel VLOOKUP Copy Cell Formatting and Row Height?
Question details
The user wants a VLOOKUP result to use the formatting and row height of the matched source cell.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses VLOOKUP to retrieve a value from another cell and needs the result to look like the source.
- Observed behavior
- VLOOKUP returns the value but does not transfer font settings, colors, text wrapping, or row height.
Identify the lookup key, the source cell that contains the returned value, and the destination cell. Check whether the lookup uses an exact match, since the VBA example below is designed for exact matches.
Use VBA to copy the matched cell format and row height
Use this approach in desktop Excel when you want a macro to apply the format and row height of the exact-match source cell.
Worksheet formulas return values, not source formatting. This example assumes that Sheet2!A2 contains the lookup key, Sheet1!A2:A100 contains the source keys, Sheet1 column B contains the returned values, and Sheet2!B2 contains the VLOOKUP formula. Change the sheet names and ranges to match your workbook.
The macro copies formatting without replacing the formula. It also changes the height of the entire destination row, not just the result cell. Run it again when the lookup changes; it does not update formatting automatically.
In desktop Excel, open Developer > Visual Basic, then choose Insert > Module.
Paste this code into the module: Sub CopyLookupFormat() Dim src As Worksheet, dst As Worksheet Dim matchPos As Variant Dim sourceCell As Range Set src = ThisWorkbook.Worksheets("Sheet1") Set dst = ThisWorkbook.Worksheets("Sheet2") matchPos = Application.Match(dst.Range("A2").Value, src.Range("A2:A100"), 0) If IsError(matchPos) Then MsgBox "No exact match was found." Exit Sub End If Set sourceCell = src.Range("B2:B100").Cells(matchPos, 1) sourceCell.Copy dst.Range("B2").PasteSpecial Paste:=xlPasteFormats dst.Range("B2").EntireRow.RowHeight = sourceCell.EntireRow.RowHeight Application.CutCopyMode = False End Sub
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). In Developer > Macros, select CopyLookupFormat and click Run.

Copy the source formatting and row height manually
Use this method for an occasional lookup result when you do not want to use a macro.
Format VLOOKUP results in WPS Spreadsheets
Use VLOOKUP to retrieve a value in WPS Spreadsheets, then manually copy the matched cell's formatting and set the destination row height. The formatting will not update automatically when the match changes.
- 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and identify the lookup key and source table.
- 2. Enter the lookup formula: Select the destination cell and enter an exact-match formula such as =VLOOKUP(A2,Sheet1!A2:B100,2,FALSE), adjusting the references to your sheet.
- 3. Copy the cell format: Select the matched source cell, click Home > Format Painter, and click the formula cell.
- 4. Set the row height: Check the source row height through Home > Format > Row Height, then select the destination row and enter the same height through the same menu.

Frequently Asked Questions
Why doesn't VLOOKUP copy the source cell's color or font?
VLOOKUP returns a value, not the source cell's formatting. Use Home > Format Painter to copy the appearance, or use VBA to apply formatting from a matched cell.
Can I copy formatting without replacing the VLOOKUP formula?
Yes. Select the source cell, use Home > Format Painter, and click the formula cell. You can also use Paste Special > Formats to avoid replacing the formula.
Will copied formatting update when the lookup result changes?
No. Manually copied formatting stays in place until you change it. The example macro must also be run again to apply formatting for a new match.
Does changing the result cell's row height affect other cells?
Yes. Row height applies to the entire row. Select the row and use Home > Format > Row Height to inspect or change it.
Can I run the VBA example in Excel for the web?
No. VBA macros cannot be run in Excel for the web. Open the workbook in a desktop version of Excel that supports VBA, or copy the formatting manually.




