How to copy source formatting of the lookup cell when using Vlookup in Excel?
In the previous articles, we have talked about keeping background color when vlookup values in Excel. Here in this article, we are going to introduce a method of copying all cell formatting of the resulting cell when doing Vlookup in Excel. Please do as follows.
Supposing you have a table as below screenshot shown. Now you need to check if a specified value (in column E) is in column A and return corresponding value with formatting in column C. Please do as follows to achieve it.
1. In the worksheet contains the value you want to vlookup, right-click the sheet tab and select View Code from the context menu. See screenshot:
2. In the opening Microsoft Visual Basic for Applications window, please copy below VBA code into the Code window.
VBA code 1: Vlookup and return value with formatting
Sub Worksheet_Change(ByVal Target As Range) 'Update by Extendoffice 20211203 Dim I As Long Dim xKeys As Long Dim xDicStr As String On Error Resume Next Application.ScreenUpdating = False Application.CutCopyMode = False xKeys = UBound(xDic.Keys) If xKeys >= 0 Then For I = 0 To UBound(xDic.Keys) xDicStr = xDic.Items(I) If xDicStr <> "" Then Set xRg = Application.Range(xDicStr) xRg.Copy Range(xDic.Keys(I)).PasteSpecial xlPasteFormats Else Range(xDic.Keys(I)).Interior.Color = xlNone End If Next Set xDic = Nothing End If Application.ScreenUpdating = True Application.CutCopyMode = True End Sub
3. Then click Insert > Module, and copy the below VBA code 2 into the Module window.
VBA code 2: Vlookup and return value with formatting
Public xDic As New Dictionary 'Update by Extendoffice 20211203 Function LookupKeepFormat(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long) Dim xFindCell As Range On Error Resume Next Application.ScreenUpdating = False Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole) If xFindCell Is Nothing Then LookupKeepFormat = " " xDic.Add Application.Caller.Address, " " Else LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address(External:=True) End If Application.ScreenUpdating = True End Function
4. Click Tools > References. Then check the Microsoft Script Runtime box in the References – VBAProject dialog box. See screenshot:
5. Press the Alt + Q keys to exit the Microsoft Visual Basic for Applications window.
6. Select a blank cell adjacent to the lookup value, and then enter formula =LookupKeepFormat(E2,$A$1:$C$8,3) into the Formula Bar, and then press the Enter key.
Note: In the formula, E2 contains the value you will lookup, $A$1:$C$8 is the table range, and number 3 means that the corresponding value you will return locates in the third column of the table. Please change them as you need.
7. Keep selecting the first result cell, and then drag the Fill Handle down to get all results along with their formatting as below screenshot showed.
- How to vlookup and return background color along with the lookup value in Excel?
- How to vlookup and return date format instead of number in Excel?
- How to use vlookup and sum in Excel?
- How to vlookup return value in adjacent or next cell in Excel?
- How to vlookup value and return true or false / yes or no in Excel?