How Can I get my VLOOKUP formula to also bring the Font background color with

[jordan1214]

Hello, I am currently using a VLOOKUP formula that brings data from another workbook. The data I am grabbing has a blue background, but when I use the vlookup formula in my new sheet and bring the cell over, it doesn't bring the blue background.

What can I do to enable the background color of my cell makes it over to my new sheet using the vlookup formula?

Thanks alot!

[FDibbins]

Short answer - you cant. pretty much all excel formulas just work with data, they do nothing with formatting. If there is some logic to the color being applies, you may be able to use Conditional Formatting

If you are familiar with CF, give it a try, if not, upload a sampe workbook, showing what you are working with, a few samples of your expected outcome, and how you arrived at that (dont include any sensitive data in your file)

[jordan1214]

Thanks for the advice, I uploaded 2 basic spreadsheets.

sample2.xlsx

sample1.xlsx

"Sample2" is where I pull the data from.

"Sample1" is where I run the VLOOKUP. I do this in cell P2. I am trying to find the largest cell out of the ffollowing group from "Sample2": AB2, AD2, AF2, AH2, AJ2.

The formula works as it finds the biggest cell (AB2).

However, i have a conditioning formula set on the cell that makes the background a light purple and it does not show up as this in my formula cell (P2 - "sample1)

Thanks!

[FDibbins]

Looking at sample2 file, it seems rather random in the way you have applied the CF? you apply it 1 cell at a time, and then apply 1 rule to that cell. Normally, CF gets applied to a range, with a series of rules. Also, is it necessary to have the data in 2 separate files? It would make things a bit simpler if they were in the same file, but its not essential.

Another question would be regarding the series of vlookups you are using in sample1. is there a reason you cant just use a MAX() on sample2 in BD or somewhere, and run the max() there, then just use a single vlookup to return the max?

[jordan1214]

I actually just put together those samples from my real database in order to not send sensitive data.

To answer the cond formatting question: I want each of those columns to be a different color to distinguish them from each other. So the color is what will tell me what I need to know. So it's not relevant to the specific data.

I just want to be able to find out how to get to move a cell including the cond formatting to a new sheet (and not lose the background color - which is my single issue here). If it's not possible I will just give up, just thought I would give it a shot.

[jordan1214]

And the reason I am using multiple workbooks is because I am using one to accumulate all of my data and the second workbook will be the one I use to run all my formula where it is organized and I only pull the data I need.

[jordan1214]

Another question would be regarding the series of vlookups you are using in sample1. is there a reason you cant just use a MAX() on sample2 in BD or somewhere, and run the max() there, then just use a single vlookup to return the max?

I'm not really sure I understand but this clearly sounds like something that is simpler and I should be able to figure out how to do. Once I figure out my main issue I will look back on this and see if I can figure out what you mean to make it more efficient for me. Thanks for the help so far.

[FDibbins]

If there is no actual logic behind the coloring of the cells, then I cant see how CF will help with the "carry-over". And like I said, formulas work with data, and formatting is not data, its cosmetics. Perhaps some-one can put some VBA together to do this for you though.

In the mean time, take a look at this suggestions.

on sample2 in BD2, use this, copied down (add any extra references you may need)...
=MAX(AB2,AD2,AF2,AH2,AJ2)
then in sample1, P2, you can use this simple vlookup()...
=VLOOKUP(B2,[sample2.xlsx]Sheet1!$A$2:$BD$20,56,0)

It doesnt answer your question, but might make your file simpler

[jordan1214]

If there is no actual logic behind the coloring of the cells, then I cant see how CF will help with the "carry-over". And like I said, formulas work with data, and formatting is not data, its cosmetics. Perhaps some-one can put some VBA together to do this for you though.

In the mean time, take a look at this suggestions.

on sample2 in BD2, use this, copied down (add any extra references you may need)...
=MAX(AB2,AD2,AF2,AH2,AJ2)
then in sample1, P2, you can use this simple vlookup()...
=VLOOKUP(B2,[sample2.xlsx]Sheet1!$A$2:$BD$20,56,0)

It doesnt answer your question, but might make your file simpler

Thanks, this definitely helps.

And as for the conditional formatting...I am just going to want entire columns of data to be individual colors, if there's any way I can twist that around to make it work I am flexible with that. It sounds like it's pretty unlikely I will be able to get that formatting to move over though in a formula.

[FDibbins]

Nope, you wont be able (at all) to get the formatting to move through a formula, but hmm I just had a thought...

now that you have a helper column in sample2 pulling out the MAX() for you, it will be a very simple matter to use that to pull which column that MAX() is in. Once you have that column number, you can pull that over to sample1, and then use CF - based on that number - to apply color to the answer to the vlookup.

[FDibbins]

Add this to sample2 BE, copied down...
=MATCH(BD2,AB2:AL2,0)

then in sample1, replace the formula in P with this...
=VLOOKUP(B2,[sample2.xlsx]Sheet1!$A$2:$BE$20,56,0)
and add this in Q (or put it wherever you want)...
=VLOOKUP(B2,[sample2.xlsx]Sheet1!$A$2:$BE$20,57,0)
this will pull in the column number containing the MAX(), now all you have to do is construct series of CF rules to color P based on the number in Q

[jordan1214]

Add this to sample2 BE, copied down...
=MATCH(BD2,AB2:AL2,0)

then in sample1, replace the formula in P with this...
=VLOOKUP(B2,[sample2.xlsx]Sheet1!$A$2:$BE$20,56,0)
and add this in Q (or put it wherever you want)...
=VLOOKUP(B2,[sample2.xlsx]Sheet1!$A$2:$BE$20,57,0)
this will pull in the column number containing the MAX(), now all you have to do is construct series of CF rules to color P based on the number in Q

Great I am going to implement this into my bigger data set now and then I will come in here and mark the thread completed. Thanks so much for your help, it's really appreciated!

[FDibbins]

Happy to help and thanks for the feedback