Vlookup that copies formatting?

[Khodex]

Hey there, I tried googling around and couldn't find a good answer for what I need.
I am making a small information lookup tool in excel where I select a company name and it displays the information.
One of the cells is for comments and I want the comment cell to retain formatting when it gets put into the lookup sheet.
Some words are bold, colored, or both and I want to keep that information. I am fine working in vba but most of my vba knowledge stems from working in Access so I am not sure how to accomplish this.

I was going to just create a function that uses vlookup to locate the cell location and then copy the contents with formatting but the problem is I am not sure how to do that.
I am open to better methods or ideas that accomplish the same goal!

I haven't decided on how big I want the cell/box for this function to go into is yet either as the amount of text varies. I assume that if I just copy the text with formatting though it will follow standard excel pasting rules and wont resize the cell so I can just designate a large box for comments and have it center.

 

[awoohaw]

Formatting isn't part of a cells contents. So, it can't be retrieved with lookup functions.
Have you considered a VBA solution?

 

[Khodex]

Formatting isn't part of a cells contents. So, it can't be retrieved with lookup functions.
Have you considered a VBA solution?

I am open to a vba solution but unsure how to do it.

 

[Eric W]

Welcome to the MrExcel forum!

There is a roundabout way to include the formatting without VBA. Be aware that this is only for appearance only, the result of the lookup cannot be used by anything else. For the examples below, I'm just using pictures, since xl2bb doesn't capture a lot of the in-cell formatting.

I built the table in E:F, I imagine yours will be similar. Next step, select cell F2, and copy it (Control-C). Then select cell B2, and Paste Special >Linked Picture (under Other Paste Options).

In C1 (or wherever you want), put this formula:

=MATCH(A2,$E$2:$E$6,0)

Now go to the Formulas tab, and click on Define Name. Set it up like this:

Click OK. Finally, click on the B2 cell, and change the formula from =$F$2 to =CommentCell like this:

Click on the check mark and you're done! If you go back to my original picture, if you change the name in A2 to one of the other names in the table, the text will show up as formatted. What's happening is that there is actually a picture that overlays the B2 cell. It dynamically changes based on the formula, which has to be a defined name. If you use the arrows to get to that cell, as opposed to just clicking on it, you'll see in the formula bar that there's nothing in it. So it's view only. If you need something else, then you'll need VBA. I'll leave that to awoohaw if that's your preference.