• VLOOKUP for Pictures

    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:

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

    Comments 18 Comments
    1. JeffreyWeir's Avatar
      JeffreyWeir -
      This is very cool, Ken.
    1. LuisPacheco's Avatar
      LuisPacheco -
      Really Cool --Good Job
    1. Ken Puls's Avatar
      Ken Puls -
      Thanks guys.
    1. royUK's Avatar
      royUK -
      I use something similar for Dashboards, enabling the user to change Charts & even data views.
    1. rizwan.planning's Avatar
      rizwan.planning -
      Wallha very good
    1. Victoria's Avatar
      Victoria -
      I have tried this method and actually a similar method and I get an "Reference not valid" when it comes to changing the picture formula to =Day_01. Any suggestions on what I am doing wrong?
    1. amit4blog's Avatar
      amit4blog -
      It's great...
    1. Iryna's Avatar
      Iryna -
      Hi, I have the same problem as Victoria had. Are you sure, we select A4:E5 in this step and not A3:E4?
      thanks

      • Select cells A4:E5
      • Go to Formulas -->Defined Name -->Create from selection
      • Choose to created names from values in the Top Row (only)
    1. okili's Avatar
      okili -
      Quote Originally Posted by Iryna View Post
      Hi, I have the same problem as Victoria had. Are you sure, we select A4:E5 in this step and not A3:E4?
      thanks

      • Select cells A4:E5
      • Go to Formulas -->Defined Name -->Create from selection
      • Choose to created names from values in the Top Row (only)
      Hi, Victorya and Iryna, try this:
      First just create names without Indirect formula, then change formula of picture to that name and after this add indirect formula. This would circumvent the "Reference not valied" error.
    1. Trianna's Avatar
      Trianna -
      Hello,

      This is incredible, worked perfectly and is very close to being exactly what I need!!!

      Now, is there away that I can have the dropdown fill the pictures across mutiple rows and columns instead of only the single row.

      Thanks!
    1. MarredCheese's Avatar
      MarredCheese -
      This is a nice post, but I figured out that it is possible to do this in a much more more scalable way that works a lot more like VLOOKUP. VLOOKUP does not require you to name each cell in your table, and neither should a picture-based equivalent.

      The key is that the picture's formula doesn't have to be a name; it can also be a plain old reference, like A1. Therefore, if you can describe the location of the picture you want to show, you can point to it without bothering with naming all the cells containing picture choices. Now, you can't use VLOOKUP itself because it returns the value of the cell rather than a reference to it. Instead you have to use MATCH, ADDRESS, and INDIRECT.

      MATCH allows you to find the row your picture is in based on its name, like 5
      ADDRESS allows you take the result from MATCH and build a string containing the address, like "A5"
      INDIRECT converts that string into a reference that the dynamic picture can use

      Here's the step by step process:

      1) make a table with one column for picture names and one column for actual pictures. Make sure the pictures each fit into a single cell.

      2) open the name manager and create a new name called "picture" with a formula that returns a reference to the desired picture's cell like this:
      =INDIRECT(ADDRESS(picture_table_first_data_row_number - 1 + MATCH(picture_name_choice, picture_name_list, 0), picture_column_number))

      3) click the picture that you want to be dynamic and change it's formula to "=picture" (without the quotes)

      If you want the example to be in terms of regular references instead of names, here's an example of that. Say your table containes 20 pictures. It has picture names in C6:C26 and actual pictures in D6: D26. The cell containing the picture name choice is A1. The formula for the name "picture" would then be this:

      =INDIRECT(ADDRESS(5 + MATCH(A1 ,C6:C26, 0), 4))
      C6:C26 is the list of names to search
      5 is the number of the first row of data minus 1 to shift the result from MATCH appropriately
      4 is the number of the column containing the pictures
      0 specifies an exact match
    1. CultofExcel's Avatar
      CultofExcel -
      Quote Originally Posted by MarredCheese View Post
      This is a nice post, but I figured out that it is possible to do this in a much more more scalable way that works a lot more like VLOOKUP. VLOOKUP does not require you to name each cell in your table, and neither should a picture-based equivalent.

      The key is that the picture's formula doesn't have to be a name; it can also be a plain old reference, like A1. Therefore, if you can describe the location of the picture you want to show, you can point to it without bothering with naming all the cells containing picture choices. Now, you can't use VLOOKUP itself because it returns the value of the cell rather than a reference to it. Instead you have to use MATCH, ADDRESS, and INDIRECT.

      MATCH allows you to find the row your picture is in based on its name, like 5
      ADDRESS allows you take the result from MATCH and build a string containing the address, like "A5"
      INDIRECT converts that string into a reference that the dynamic picture can use

      Here's the step by step process:

      1) make a table with one column for picture names and one column for actual pictures. Make sure the pictures each fit into a single cell.

      2) open the name manager and create a new name called "picture" with a formula that returns a reference to the desired picture's cell like this:
      =INDIRECT(ADDRESS(picture_table_first_data_row_number - 1 + MATCH(picture_name_choice, picture_name_list, 0), picture_column_number))

      3) click the picture that you want to be dynamic and change it's formula to "=picture" (without the quotes)

      If you want the example to be in terms of regular references instead of names, here's an example of that. Say your table containes 20 pictures. It has picture names in C6:C26 and actual pictures in D6: D26. The cell containing the picture name choice is A1. The formula for the name "picture" would then be this:

      =INDIRECT(ADDRESS(5 + MATCH(A1 ,C6:C26, 0), 4))
      C6:C26 is the list of names to search
      5 is the number of the first row of data minus 1 to shift the result from MATCH appropriately
      4 is the number of the column containing the pictures
      0 specifies an exact match
      I've tried this but it's not working.

      The address and match part appear to work, I've entered =ADDRESS(23+MATCH(N8,'Drivers & Teams'!$A$24:$A$34,0),2) into a blank cell and it's returning $B$31 which is the location of the relevant picture. I then open name manager, create new and name it "Teams", I then reference to =INDIRECT(ADDRESS(23+MATCH(N8,'Drivers & Teams'!$A$24:$A$34,0),2)).

      When I attempt to reference a picture in the formula box to =Teams it states "reference not valid". My only thought was perhaps because the picture and list were in different tabs that it might be breaking but I tried a similar thing in the same tab and it had the same problem.

      Congratulations on the formula any way - it's very clever.
    1. MarredCheese's Avatar
      MarredCheese -
      Quote Originally Posted by CultofExcel View Post
      I've tried this but it's not working.

      The address and match part appear to work, I've entered =ADDRESS(23+MATCH(N8,'Drivers & Teams'!$A$24:$A$34,0),2) into a blank cell and it's returning $B$31 which is the location of the relevant picture. I then open name manager, create new and name it "Teams", I then reference to =INDIRECT(ADDRESS(23+MATCH(N8,'Drivers & Teams'!$A$24:$A$34,0),2)).

      When I attempt to reference a picture in the formula box to =Teams it states "reference not valid". My only thought was perhaps because the picture and list were in different tabs that it might be breaking but I tried a similar thing in the same tab and it had the same problem.
      Attachment 2713
      Congratulations on the formula any way - it's very clever.
      Thanks for the compliment. I'm not sure from looking at your formulas what the problem is, but it definitely works for me. I should have mentioned this before, but if the dynamic picture is on a different worksheet then the table of pictures, you have to factor that into your formula, like this:

      =INDIRECT("'picture table'!" & ADDRESS(...))

      I attached an example of a dynamic picture done two ways: 1) on the same worksheet as the picture table, 2) on a different worksheet.

      Attachment 2713
    1. Harvey's Avatar
      Harvey -
      I've added links to your posting and one of the comments above to stack overflow.

      I Hope this is ok with you.

      stackoverflow.com questions/28489836/ how-to-show-a-picture-in-a-cell-depending-on-the-value-of-another-cell

      Harvey
    1. jzrit580's Avatar
      jzrit580 -
      Quote Originally Posted by MarredCheese View Post
      Thanks for the compliment. I'm not sure from looking at your formulas what the problem is, but it definitely works for me. I should have mentioned this before, but if the dynamic picture is on a different worksheet then the table of pictures, you have to factor that into your formula, like this:

      =INDIRECT("'picture table'!" & ADDRESS(...))

      I attached an example of a dynamic picture done two ways: 1) on the same worksheet as the picture table, 2) on a different worksheet.

      Attachment 2713
      Your example works great but I have a different reason for needing this. I need this to function as an actual vlookup type of worksheet for 50+ cells and needed to make it work on multiple sheets weekly. So instead of just having one absolute reference that changes that one particular picture, I would need it to be able to function on multiple rows.

      For instance, let's say column A consists of the pictures that I need to update automatically based on column B. If B2 said "Hex" and B3 said "Hex Washer", I'd want the corresponding column A pictures to both update. Is this even possible without having multiple name ranges?

      I tried using a relative reference for the "choice" name range as =interface!$B2 instead of =interface!$B$2 but that makes the whole thing stop working. Maybe this just isn't possible
    1. Norrbysa's Avatar
      Norrbysa -
      Hi!

      I've used this guide and it works for me.
      I have the list of pictures and names on one sheet and the dynamic picture on another.

      HOWEVER!

      Here's my nut I cannot seem to crac:
      How can I create a expandable "pull-the-corner-list" of dynamic pictures?

      Using your example above:
      Showing weather images for 50 days and then exand the list downwars to 100 days or more.

      In the example I would need to create one name for each new dynamic picture.
      Is there another way?

      I would be extremely happy if I could get some help solving this.
      I have already spent 2 days on this (rokkie).
    1. as02011961's Avatar
      as02011961 -
      Hi to all,

      I always are astonished what magic work can be done with EXCEL. Thanks for that.

      Regards

      Andreas
    1. as02011961's Avatar
      as02011961 -
      Hi to all,

      this is very cool stuff!

      Regards

      Andreas