Excel VLOOKUP function to keep text format

Hello,

I am using vlookup function in Excel 2013, it works awesome.

My problem is that the function does not bring the formatting of the text / value, it only bring the raw value.

I understand that there is a way to use "VBA" to do this.

Can anyone help me with a script for VBA or other solution?

Here is an example of the excel I am working with:

SMO      Due to the presence of a soffit to the underside of the stairs and narrow carpet to the top surface the margins only could be inspected.
STSO      Due to the presence of a soffit to the underside of the stairs our inspection was confined to the top surface only.
SSL      The stairs are of solid construction.

When I type the Code and press tab/enter it auto-completes with the description.

The formula I use is simple: =VLOOKUP(A1,Data,2,FALSE)

Thank you!
George
georggAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

regmigrantCommented:
You can use copy, paste formatting to apply the format to the target column and it will be applied whenever the formula returns a value.
If the area you need the results in can grow over time then you can define it as a table (insert table) and formatting will be automatically added when each new row is created

if you post a sample I can give you an example
0
gowflowCommented:
As mentioned we need a sample workbook.
gowflow
0
georggAuthor Commented:
Here is a sample. thanks.
Sample-autocomplete.xlsx
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

gowflowCommented:
Well here is your solution. It is all VBA and no need for your formula.

Simply type in the code in the sheet Print to Cust in Col B (you can put as many codes as  you want in Col B and it will put the corresponding description in Col C.

Pls check the attached file and make sure you Enable macros prior to trying.

for your easy reference here is the main code for that.

Sub GetFormat(Target As Range)
Dim Rng As Range
Dim sValue As String
Dim sFormula As String
Dim WS As Worksheet
Dim ThisWS As Worksheet
Dim cCell As Range

Set ThisWS = ActiveSheet
Set WS = Sheets("Data Sheet")
For Each Rng In Target
    sValue = Rng.Value
    sFormula = Rng.Formula
    Set cCell = WS.UsedRange.Find(what:=sValue, LookIn:=xlFormulas, lookat:=xlPart, MatchCase:=False)
    If Not cCell Is Nothing Then
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
        cCell.Offset(, 1).Copy
        ThisWS.Range(Rng.Offset(, 1).Address).PasteSpecial
        Rng.Select
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If
Next Rng

End Sub

Open in new window


Let me know your comments.
gowflow
Sample-autocomplete.xlsm
2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
georggAuthor Commented:
You are a GOD! :) Thanks!
0
gowflowCommented:
WOW thanks for the compliment way far from being a GOD! simply good in finding suitable solution in excel. Glad you liked it.
gowflow
1
georggAuthor Commented:
thanks again:)
0
gowflowCommented:
Please feel free to put a link in here for any other question you may need help with.
gowflow
0
georggAuthor Commented:
Hi again gowflow,

I have an issue with this, everything was working fine, but I found an error, maybe you can help with that as well.

I have in the list of codes REC, that pops up like 20 times with the Description RECOMMENDATIONS, when I do this in col. b under Print to Cust it takes info from D-11 position.

As well I have RC and auto-completes from GRC info.

Is this a simple tweak?

Thank you.
Template-Experts.xlsm
0
gowflowCommented:
Fixed.
gowflow
Template-Experts.xlsm
0
georggAuthor Commented:
Thank you so much for all your trouble and all your time.

REC is pulling comments as well.

Is It something I can do to fix these errors?

Thanks.
0
gowflowCommented:

REC is pulling comments as well.

Sorry but I don't understand what you want ? what is expected when you input REC ??

If any exception to the rule please be clear in mentioning them as we are wasting time here for unclear instructions.
gowflow
0
georggAuthor Commented:
Hi gowflow,

I am sorry for the misunderstanding. Everything is fine.

I just needed to refresh.

Thank you so much!

George
0
gowflowCommented:
Your welcome.
gowflow
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.