gbpfan1231 Posted March 25, 2011 Share Posted March 25, 2011 OK we have a table with certain parts highlighted different colors. Someone wants to use a Vlookup in a different tab and have it bring back the value AND the shaded color. Is this possible? A simple example would be.... A B 1 X RED 2 Y Blue 3 Z Yellow So if you wrote a Vlookup formula like =Vlookup(D1,A1:B3,2,False) with D1 = X He wants the value RED to come back and automatically be bolded. I can't think of any way to do this but what he is trying to do is actually quite logical and would be very helpful. Any ideas? Quote Link to comment Share on other sites More sharing options...
lennykravitz2004 Posted March 26, 2011 Share Posted March 26, 2011 OK we have a table with certain parts highlighted different colors. Someone wants to use a Vlookup in a different tab and have it bring back the value AND the shaded color. Is this possible? A simple example would be.... A B 1 X RED 2 Y Blue 3 Z Yellow So if you wrote a Vlookup formula like =Vlookup(D1,A1:B3,2,False) with D1 = X He wants the value RED to come back and automatically be bolded. I can't think of any way to do this but what he is trying to do is actually quite logical and would be very helpful. Any ideas? Use conditional formatting on the cells where the vlookup formula resides. Not possible with "just" a formula AFAIK. Quote Link to comment Share on other sites More sharing options...
Big Country Posted March 26, 2011 Share Posted March 26, 2011 Use conditional formatting on the cells where the vlookup formula resides. Not possible with "just" a formula AFAIK. My initial thought as well. Quote Link to comment Share on other sites More sharing options...
gbpfan1231 Posted March 26, 2011 Author Share Posted March 26, 2011 Use conditional formatting on the cells where the vlookup formula resides. Not possible with "just" a formula AFAIK. Would not work. Way too many possibilities. That may work with my simple example. Here is a bit longer example of what we are trying to do. We are moving to SAP and we have a product hierarchy that has 7 levels. Our company has to build this Hierarchy from scratch and there really is nothing that would stop the person building it from making a mistake. So for example we have a ton of combinations - we make rods and we make beds - selections of each part of the hierarchy makes sense if it is a rod and does not make sense if it is a bed. So he went through all the possibilities in each level and shaded the rod related cells to red and the bed related cells to blue. Now as the hierarchy was built the person building it if they chose "03" as the first level it would come back with "beds" and be Blue shaded. Then if they chose "02" for the second level it would come back as "Chrome" and be shaded Red (because it is Rod related). The person would know immediately that they have something wrong because they see two different shades. To me it makes very logical sense but some of these levels have over 50 possibilities so conditional formatting is out the window. I doubt it is possible. I was thinking that if there was some function that could find a cell value and bring back that "Format" and use it embedded in the Vlookup it may work??? Quote Link to comment Share on other sites More sharing options...
lennykravitz2004 Posted March 26, 2011 Share Posted March 26, 2011 (edited) Results seem to point to VBA. ETA: more than 64 combos of formatting are possible/needed? General cell formatting cannot be applied by a formula, so VLOOKUP cannot do this for you. It would need to be VBA. http://www.mrexcel.com/forum/showthread.php?t=535466 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ No, native formulas cannot detect formatting and apply it to your lookup cell. The only way to change the formatting of the lookup cell is to use: 1) manually applied formatting 2) Conditional formatting based on the value of the cell/formula (this might work for you) 3) use VBA instead of formulas to do the work and physically copy the matched cell to the lookup cell http://www.excelforum.com/excel-worksheet-...ookup-cell.html Edited March 26, 2011 by lennykravitz2004 Quote Link to comment Share on other sites More sharing options...
gbpfan1231 Posted March 26, 2011 Author Share Posted March 26, 2011 Results seem to point to VBA. ETA: more than 64 combos of formatting are possible/needed? http://www.mrexcel.com/forum/showthread.php?t=535466 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ http://www.excelforum.com/excel-worksheet-...ookup-cell.html Maybe it could be added in future versions and be on a gay commercial like the windows 7 ads. Thanks for the help - your answers proved what I was thinking. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.