Project Management Heat Map Within Register

**Christopher Val** · 10-16-2014, 04:24 PM

Hi,

I've been searching the net like crazy for a solution. I am struggling here. I have a tried numerous techniques, yet I cannot get anything to incorporate my Heat Map Pictures into the Risk Register.

MY Issue: When someone selects the Probability to be "VeryHigh" and the Impact to be "VeryHigh" I would like the Heat Map Image "VeryHighVeryHigh" (on another sheet) to populate in the next cell. I would like this to continue for at least 200 Risks/Opportunities. I can't find anything like this and the solutions I have found just don't work/apply. Does anyone have something like this or can help me?

I Have Tried:
http://www.youtube.com/watch?v=P4XriuikOs8
http://www.youtube.com/watch?v=MzF_5DXhqbs
http://www.mcgimpsey.com/excel/lookuppics.html
http://excel.tips.net/T003128_Displa..._a_Result.html

Chris

p.s. I've attached the Excel File that currently references a Heat Map Dashboard that needs to remain unbroken.

**ben_hensel** · 10-16-2014, 04:32 PM

Sounds like you want to do what's generally called a "picture lookup"; here's a white paper on it by Ken Puls.

You'll probably need to run an INDEX(MATCH, MATCH) instead of an array match like VLOOKUP; that's just me guessing based on your description.

**Christopher Val** · 10-16-2014, 04:41 PM

Sorry here's the file. I've reviewed http://www.cga-pdnet.org/pdf/VLOOKUPforPictures.pdf before and found that once I use the formula it will not work.

I do not know what I am doing wrong or how to fix this. It's driving me crazy.

**ben_hensel** · 10-16-2014, 06:40 PM

I think you might be going down the rabbit hole.

Just fill out the table with "Risk Scores" that you can associate with the colors, and then use Conditional Formatting to automatically apply the cell color you you want. That will be better than trying to imbed that entire matrix in each of those little cells IMHO.