Function Problems

Can Excel VLOOKUP Copy Cell Formatting and Row Height?

Maira MehtabMaira Mehtab Nov 20, 2026 870 views

Question details

The user wants a VLOOKUP result to use the formatting and row height of the matched source cell.

Can Excel VLOOKUP Copy Cell Formatting and Row Height?
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In desktop Excel, open Developer > Visual Basic, then choose Insert > Module.

2
Add the macro

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

3
Save and run the macro

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). In Developer > Macros, select CopyLookupFormat and click Run.

Use VBA to copy the matched cell format and row height
Match the lookup setup: Use an exact-match VLOOKUP, such as =VLOOKUP(A2,Sheet1!A2:B100,2,FALSE), with the same key and source range as the macro. Enable macros only in workbooks you trust.
Work with lookup results in WPS

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. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and identify the lookup key and source table.
  2. 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. 3. Copy the cell format: Select the matched source cell, click Home > Format Painter, and click the formula cell.
  4. 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.
Retrieve matching values with VLOOKUP and format the result separatelyOpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet formatting toolsWork with spreadsheets using the free WPS Office suite
microsoft office alternative - wps office

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.