Copy formatting in VLOOKUP

[davehnj]

When doing a vlookup of an existing table, is there anyway to bring the formatting along with the data? For instance, if a cell in the table is red, can this format setting be brought into the cell from which the lookup is started? VBA solutions would be great too.

 

[Peter_SSs]

Welcome to the MrExcel board!

General cell formatting cannot be applied by a formula, so VLOOKUP cannot do this for you. It would need to be VBA. If you provided more details about where your current formula(s) is/are, what the formula is (so we know the type of lookup, the value being looked up and the range being looked in) etc then somebody might be able to provide some help with the vba.

 

[davehnj]

Peter, thank you so much for your quick reply. The table is in the same sheet and has item descriptions that are formatted in red or green as needed. but when they are "looked up" and brought to the vlookup cell, the formatting is gone! Only the formatting in the vlookup cell is active. If the description in the lookup table was red, it is not when brought to the vlookup cell. As you described, only the text is brough over, not the formatting. If possible, could the vlookup provide the cell address where the description can be found as a hyperlink so the user can directly go to the description in the original cell (in the lookup table)? For instance, instead of bringing back the esults of the vlookup, is it possible to use index and match to brong back the cell address and wrap that in a hyperlink so the user can immediately jump to description with the formatting? Sorry for rambling on.

 

[Peter_SSs]

Sorry for the slow response but see if this sort of thing is any use. Hyperlinks are not a strong point of mine so I don't know if this is the best way but I think it does what you want. If cell A2 is changed to, say, 'b' then cell B2 will display '2' and will be hyperlinked to cell H3.

My worbook that contains this sheet is called 'Hyperlink Test.xlsx' so you would need to change that and the sheet name to match your names.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	H1	H2					H1	H2
2	c	3					a	1
3							b	2
4							c	3
5							d	4
6
Sheet 4

 

[davehnj]

Thanks again Peter for trying to work on my problem. I just cannot seem to get this to work. I keep getting a message from Excel when I try to jump to the hyperlink - "Bad reference". I know that I must be doing something wrong, but I cannot surmise what that could be! I'll keep trying. Thanks.

 

[Peter_SSs]

Thanks again Peter for trying to work on my problem. I just cannot seem to get this to work. I keep getting a message from Excel when I try to jump to the hyperlink - "Bad reference". I know that I must be doing something wrong, but I cannot surmise what that could be! I'll keep trying. Thanks.

For a start, try setting up a new workbook to replicate what I have done. That is ..

- call your workbook 'Hyperlink Test.xlsx' (Unless you are using a pre-2007 Excel version in which case you would name it 'Hyperlink Test.xls'. BTW, what version are you using?)

- name the sheet 'Sheet 4'

- set up the data and formula just as I have (adjust the formula if your workbook is an '.xls' instead of '.xlsx)

Does that work?

If so, then we have to start working on what is different between your workbook and mine to see if we can adapt to make it work for you.