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.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
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 20180706 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 Range(xDic.Items(I)).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 20180706 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 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?
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 10 days agohi i got the error "compile Error: Ambigious name detected: xDic
To post as a guest, your comment is unpublished.· 1 months agohi i got the error "compile Error: Ambigious name detected: xDic
To post as a guest, your comment is unpublished.· 2 months agoHello, Thanks for the code. I do not get any error message but the formula only works as a normal vlookup would. Could you please assist? Thanks for your time.
To post as a guest, your comment is unpublished.· 3 months agoHello, I've been using the above code in Excel 2010 with no problems to date. However, I was recently upgraded to Office 2016 and now the code crashes Excel every time I try to fill down more than one row. Unfortunately, it is not giving me an error other than "Microsoft Excel has stopped working". I was wondering if you have come across this issue previously, and if there is something I need to do to make it work in 2016. Thanks!
To post as a guest, your comment is unpublished.· 6 months agoHello. I created a blank spreadsheet and duplicated your example in Excel 2013, but keep getting a Compile error: Syntax error and Dim I As Long is highlighted. Is there something I'm missing? I would love to get this working. Thank you.