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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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?
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.· 3 months agoI get the same Ambiguous name error - has anyone managed to solve it ?
To post as a guest, your comment is unpublished.· 9 months agoHere is the file and pic
To post as a guest, your comment is unpublished.· 9 months agoHI, I am new to using VBA and tried using this code in my spreadsheet, but the text formatting on the Rec2 tab doesn't come over to Rec tab when lookup is used. Any help would be greatly appreciated. Thanks Pat
To post as a guest, your comment is unpublished.· 9 months agohi i got the error "compile Error: Ambigious name detected: xDic
To post as a guest, your comment is unpublished.· 10 months agohi i got the error "compile Error: Ambigious name detected: xDic
To post as a guest, your comment is unpublished.· 11 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.· 1 years 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.· 1 years 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.
To post as a guest, your comment is unpublished.· 1 years agoI tried this one and the the one that pulls just the color background and am getting the same error. Compile error: Ambiguous name detected. I click OK and it highlights xDic. Any suggestions? I'm not super familiar with all of this so please help/explain :) thanks in advance
To post as a guest, your comment is unpublished.· 1 years agoAlso, if I add your formula as part of an "If" statement (see below), it formats the cell however it wants LOL (or at least it seems so. One cell, the text went shadowed and bold with a top border on the cell; another cell, the text centered)
=IF($F19 = "", "",LookupKeepFormat(F19,'Item #s'!$A$1:$M$1226,2))
To post as a guest, your comment is unpublished.· 1 years agoHi,
I get no errors and it does the lookup, but because my lookup value is on another worksheet (a more likely scenario), it doesn't pull the formatting. Is there a tweak to the code that I can make for that? (Be very specific as to where the change needs to go as I'm a coding novice) Thank you! I'm excited to add this feature to one of my spreadsheets!!
To post as a guest, your comment is unpublished.· 1 years agoHi There
I have tried to use the code however I am getting the error in the attached pic. Any assisting will be greatly appreciated.
To post as a guest, your comment is unpublished.· 1 years agoGreatly appreciate the follow-up Hugo!
Unfortunately like Vi, I am too much of a novice to work out where to insert your suggested code fixes...
Thanks again, have a great day :)
To post as a guest, your comment is unpublished.· 1 years agoHey Hugo,
I have the same problem as Julia. It doesn't work on other sheets. Could you help write code for the whole function and sub worksheet? I am not sure where to replace/insert xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Nam and Sheets(Split(xDic.Items(I), "|")(1)).Range(Split(xDic.Items(I), "|")(0)).Copy
thanks in return
To post as a guest, your comment is unpublished.· 1 years agoJulia, correct this lines:
in Function LookupKeepFormat:
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Name
in Sub Worksheet_Change:
Sheets(Split(xDic.Items(I), "|")(1)).Range(Split(xDic.Items(I), "|")(0)).Copy
To post as a guest, your comment is unpublished.· 1 years agoThis is great, thank you! The only problem is, I find it works fine if I'm looking up in the same sheet, but can't get it to work when I'm trying to do a lookup in a separate sheet to the source data. Will keep trying
To post as a guest, your comment is unpublished.· 1 years agoI got the same error.
You will have to change the " " for actual "', without ';' as indicated below
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "
LookupKeepFormat = ""
xDic.Add Application.Caller.Address ""
To post as a guest, your comment is unpublished.· 1 years agoI also got the compiler error.
It gets corrected if you change the following variable with actual "". No ';' in the middle.
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "
To post as a guest, your comment is unpublished.