Something that can be very handy when you’re building a dashboard is to return a certain picture depending on a condition. We can use VLOOKUP to look up data in a table and return the corresponding value from a different column, but unfortunately we can’t do that with pictures... or can we?

This example shows how to accomplish the equivlanet of a picture VLOOKUP, and is based on looking up a picture to display the appropriate icon for a weather forecast; something we use on our dashboards from our golf course. We update the weather data daily via a weather feed, and really don’t want to have to manually update each picture, so this technique comes in really handy.

Step 1: Creating the Picture Table

The first thing we need to do is create a table of our pictures. For this example, we’ll start with each of the potential weather conditions that could be returned by a weather feed, as follows:

This list is contained in A1:A11 on the “Pictures” worksheet.

Next, we need images for the forecasts. The example file (available at the link at the bottom of the page) contains weather forecast images snapped with my favourite screen capture program (SnagIt by TechSmith) which were then pasted in the table next to the correct description:

Now, there are a couple of really important things to notice here:

• The pictures don’t all have to be the same size but they should be close
• The pictures MUST fit entirely in the cells with at least a little bit of white space around them

Next, we need to name the cells that hold the hold the pictures. Because we have a lot of them, the easiest and fastest way to do this is by using the “Create from Selection” feature:

• Select cells A2:B11
• Go to Formulas --> Defined Name --> Create from selection
• Choose to created names from values in the Left column

What this will do for us is create a defined name of “rain” for B2, “isoshower” for B3, and so on down the table. You can verify that this works by selecting cell B6 and noticing that it says “rain” in the name box:

Great, now our table is set up correctly.

Step 2: Setting Up the Driver Cell(s)

So the next step is to build the plumbing that will drive which picture will be shown. This could be done in any number of manners including any of the following:

• Data being pulled in from a query table
• Manual entry
• A VLOOKUP function
• A data validation list

The possibilities are truly endless, with the only key being, the value in the driver cell MUST match one of the names in the Picture Name list that we have in cells A2:A11. To that end, we’re going to set up a quick table that looks like this:

Cells A3:E3 are text values that were entered, but A4:E4 were set up using a data validation list in this case. To do that:

• Select cells A4:E4
• Go to Data --> Data Validation -->Data Validation
• Choose to allow a List
• Select A2:A11 on the Pictures worksheet (or just type =Pictures!$A$2:$A$11 )
• Click OK

You’ll now be able to select items from the list by clicking the dropdown arrows in the cells. Remember though, this is just for simplicity here, and you could drive these cells in via any manner or formula you wanted, so long as it returns an item that matches your list.

Now, we also need a few more names as well. In fact, we need a name for each of the individual days. So let’s set those up by doing the following:

• Select cells A4:E5
• Go to Formulas -->Defined Name -->Create from selection
• Choose to created names from values in the Top Row (only)

Cool, so now A4 has a name of “Day_01”, B4 is “Day_02”, and so on. We’re almost there, but we need to make a minor modification to these named ranges. So let’s open the Name Manager:

• Go to Formulas -->Name Manager
• Select Day_01
• Click Edit

What we need to do here is change the Refers To formula from =Forecast!$A$4 to =INDIRECT(Forecast!$A$4)

The reason for this is that cell A4 contains the text value of “rain”. What the INDIRECT function does is essentially tries to interpret the text as a formula. As it happens, we have a defined name called “rain”, which refers to cell B2 on the Pictures worksheet. So the INDIRECT function will return a reference to that cell for us!

Once you’ve updated that formula, make sure you also update the other Day_0x names as well:

• Day_02: =INDIRECT(Forecast!$B$4)
• Day_03: =INDIRECT(Forecast!$C$4)
• Day_04: =INDIRECT(Forecast!$D$4)
• Day_05: =INDIRECT(Forecast!$E$4)

Now, I know that this doesn’t look like much, but you’ve laid all the groundwork to make some magic happen!

Step 3: The Picture Lookup

What we need to do now is go and copy any picture from the Pictures worksheet. It doesn’t matter which one, but you need to do this correctly:

• Find the picture you want
• Select the cell, NOT the picture!
• Press CTRL+C to copy it
• Go to the Forecast worksheet
• Right click, and choose to Paste as a Linked Picture

Careful here! You need to get the right icon to paste with. In Excel 2010 you’ll find it here:

Now, with that done, you have one more thing to do:

• Select the newly pasted picture
• Replace the formula in the formula bar with: =Day_01

And that’s it! Try changing the value in A1 and see what happens… instant picture lookup!

To do the others, you just follow a similar route. Copy any cell, and paste as a linked picture. In fact, you can even paste the four remaining ones one after the other. Once done you need to select each picture and update the formula to =Day_02, =Day_03, etc…

Ending Thoughts

This is a really cool trick, doesn’t need a single line of VBA, and actually doesn’t even use a VLOOKUP function at all! It’s purely driven by the INDIRECT function pointing back to a cell range with the Camera object (invoked by the Paste Picture as Link ability.)

Do be warned that the camera object does have some issues, and you may not find the pictures stable if you use too many. In my experience, that’s been more than 10 linked pictures. (Although the table of pictures can have hundreds of pictures in it to choose from with no issues.)

You can download both the starting and completed versions of the file from the link below: