This post may contain affiliate links. Please read my disclosure for more info:

3 ways to change an image based on a cell value

Change image based on cell value Thumb

Change image based on cell value

From stock parts to holiday villas, from employees to logos, there are many reasons you may want to automatically change an image based on a cell value.  In this post, we’ll explore 3 different methods to achieve this task:

  • Named range + INDEX/MATCH + linked picture
  • Chart fill + #N/A
  • VBA User-Defined Function

Each solution has it’s own strengths and weaknesses; pick the one which best meets your requirements.

Let’s look at each of these methods in turn.

 

Change image with a named range + INDEX/MATCH + linked picture

We’ll start the first example with a list of countries in Column A and their relevant flags in Column B.

Image lookup - Country list

Next, we’ll create a named range by selecting Formulas -> Define Name.

Image lookup - Define Name

In the New Name window create a new named range called “CountryLookup”.

Image lookup - new name

This named range will refer to an INDEX/MATCH function containing the country name and the flags.

=INDEX(Sheet1!$B$2:$B$15,MATCH(Sheet1!$F$2,Sheet1!$A$2:$A$15,0))

In this formula:

  • Cells B2-B15 contain the cells with the flags
  • Cell F2 contains the name of the country to be found
  • Cells A2-A15 contains the names of the countries

After the named range has been created, select any cell which contains a flag, click Home -> Copy (or Ctrl + C) to copy the cell, then select a different cell, click Home -> Paste -> Linked Picture (alternatively, the Camera Tool is an option).

Image lookup - Paste Linked Image

The pasted image will appear.  Select the image, then change the address in the formula bar to be equal to the named range created earlier (“CountryLookup” in our example).

Image lookup - Linked image address

For completeness, Cell F2 has been changed into a Data Validation drop down containing all the countries.

Each time a new country is selected from the drop down the image will change automatically.

Image Lookup - Index Match GIF

The key points to remember for the named range + INDEX/MATCH + linked picture method are:

  • The INDEX/MATCH function is inserted into a named range.
  • The INDEX/MATCH function will return the cell address containing the image.
  • A linked shape is created, which uses the named range as it’s source.

 

Change image with chart fill + #N/A

The second method uses a stacked column chart to hold the images.

Columns A contains the names of countries, Column B contains an IF/VLOOKUP formula.

Image Lookup - Chart NA values

The formula in Cell B2 is:

=IF(VLOOKUP(A2,$F$2,1,0)=A2,1,NA())

The purpose of the formula is to show 1 if Cell F2 is the same as the country in Column A, else #N/A is displayed.  The formula has been copied down to the last country name in the list.

Select all the values in Column A and Column B.

From Ribbon click Insert -> Charts -> Stacked Column.

Image Lookup - Chart Stacked Column

A chart will appear.  Right-click on the series, then click Select Data…

Image Lookup - New Chart

From the Select Data Source window click the Switch Row/Column button, the Legend Entries box on the left should contain the list of countries as different chart series.

Then click OK.

Image Lookup - Switch RowColumn

Now, it is all about formatting:

  • Set the maximum Y-axis to 1
  • Delete the Y-axis
  • Delete the Chart Title
  • Delete the X-axis
  • Format the Data Series to change the Gap Width to be 0%
  • Re-position and resize the chart to fit your requirements

Select each country in turn and format the visible data series so the fill for each bar is a picture of the country’s flag.  Inserting images one at a time, in this way can be time-consuming.  The macro below will insert the images into the chart fill automatically.  The names of the countries must exactly match the names of the images.

To run the macro, change “Chart  1” to the name of your chart, and set the file path of the images (including the image type file extension).  Select the cells containing the country names and run the macro.  The chart should automatically populate with the images.

Sub InsertPicturesIntoChart()

Dim i As Integer
Dim selectedCells As Range
Dim imageFullName As String

'Select the cells with the country names before running macro
For Each selectedCells In Selection
 
    i = i + 1

    'imageFullName is the file path to the image.  
    'Country name in Column A must match the name of the image.
    imageFullName = "C:\Users\marks\Documents\Flags\" & Cells(i + 1, 1).Value & ".png"

    'Change the fill of the chart Series.  Change Chart 1 to the name of the chart.
    ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(i).Format.Fill.UserPicture imageFullName

Next selectedCells

End Sub

The fully working chart achieves a similar result to the first example.

Image Lookup - Chart GIF

The key point with this method is:

  • When #N/A is used in the chart source the value is not displayed, only the matching value will be displayed.
  • The image is used as the fill of the stacked column chart.

 

Change image with a VBA User-Defined Function

Finally, we will consider a VBA user-defined function.

The images must be saved separately within a single folder, and each file name must match exactly with the name of the country.  Copy the following code into a standard module within the Visual Basic Editor.

Public Function PictureLookup(Value As String, Location As Range)

Application.Volatile

Dim lookupPicture As Shape
Dim sheetName As String
Dim picTop As Double
Dim picLeft As Double

sheetName = Location.Parent.Name

'Delete current picture if exists
For Each lookupPicture In Sheets(sheetName).Shapes
    If lookupPicture.Name = "PictureLookup" Then
        lookupPicture.Delete
    End If
Next lookupPicture

'Get position of cell calling the UDF
picTop = Location.Top
picLeft = Location.Left

'Add the picture in the right location
Set lookupPicture = Sheets(sheetName).Shapes.AddPicture _
("C:\Users\marks\Documents\Flags\" & Value & ".png", msoFalse, msoTrue, picLeft, picTop, -1, -1)

'change the picture name
lookupPicture.Name = "PictureLookup"

PictureLookup = ""

End Function

This function can be used on the worksheet as follows:

=PictureLookup(A2,B2)

Cell A2 contains the name of the country and Cell B2 is the location to place the image.

Look at this function in action.  It behaves in a similar way to the previous examples.

Image Lookup VBA GIF

 

Using multiple images on the same worksheet

Update: 28 December 2017 – I have received a number of questions about using this formula multiple times on the same sheet.  For this solution try the code below:

Public Function PictureLookup(Value As String, Location As Range, Index As Integer)

Application.Volatile

Dim lookupPicture As Shape
Dim sheetName As String
Dim picTop As Double
Dim picLeft As Double

sheetName = Location.Parent.Name

'Delete current picture with the same Index if exists
For Each lookupPicture In Sheets(sheetName).Shapes
    If lookupPicture.Name = "PictureLookup" & Index Then
        lookupPicture.Delete
    End If
Next lookupPicture

'Get position of cell calling the UDF
picTop = Location.Top
picLeft = Location.Left

'Add the picture in the right location
Set lookupPicture = Sheets(sheetName).Shapes.AddPicture _
("C:\Users\marks\Documents\Flags\" & Value & ".png", msoFalse, msoTrue, picLeft, picTop, -1, -1)

'change the picture name
lookupPicture.Name = "PictureLookup" & Index

PictureLookup = ""

End Function

This function can be used on the worksheet:

=picturelookup(B5,H5,1)

The key difference to the previous function is the inclusion of a unique counter (highlighted in red above).  Each picture must have it’s own unique counter for the function to work correctly.  Possible ways to achieve this are:

  • Manually changing the function for each instance (manually changed to 2 in the example below)
    =picturelookup(B5,H5,2)
  • Referencing a cell which contains a unique number (C2 contains the unique counter)
    =picturelookup(B5,H5,C2)
  • Using the ROW() function (where the function is not repeated across the columns the ROW() number will be unique)
    =picturelookup(B5,H5,ROW())

 

Advantages & disadvantages of each option

Named range + INDEX/MATCH + linked picture

  • Advantage: All the images are contained on the worksheet
  • Disadvantage: Could accidentally move images outside of the cell range

Chart fill + #N/A

  • Advantage: Images contained within the chart, so difficult to change
  • Disadvantage: Time consuming to set-up
  • Disadvantage: Requires a helper column to function correctly

VBA User Defined Function

  • Advantage: No additional columns required on the worksheet
  • Disadvantage: Requires users ability to use VBA
  • Disadvantage: Image name, must be the same as the value on the worksheet

22 thoughts on “3 ways to change an image based on a cell value

  1. Joe says:

    Hey, i’m trying to implement the VBA solution to my excel file but i keep getting a #VALUE! I changed the location to the proper location where i have my images and made sure they were saved as .png images but it still doesn’t work. Do I need to change anything else in the code?

    Thank you, joe.

    • Excel Off The Grid says:

      Hi Joe,

      You shouldn’t need to change anything else in the code.

      It can be quite difficult to diagnose problems with User Defined Functions. I think the most likely cause will be an incorrect file path or file name.

      Insert the following code after Application.Volatile:
      Debug.Print “C:\Users\marks\Documents\Flags\” & Value & “.png”

      Change the file path for the one you are using. Then calculate the worksheet.

      Look at the Immediate Window (Ctrl + G if it’s not visible) in the Visual Basic Editor and confirm that the file path and file name exactly match with the location of the name you’re expecting.

      If that doesn’t help, e-mail the file to me, I will have a look.

      • Joe says:

        Thank you for the quick reply! I was actually able to figure it out and it was the file path, I forgot tho add a \ to the end of the path and that was the issue haha. Thank you for offering to help though! Also, thanks for writing this post, thiis helped me out a lot!
        Thank you, joe

      • Ted says:

        It seems I am also having an issue. If I copy paste =PictureLookup(A2,B2) I get #VALUE!… When editing the cell directly, it gives me a syntax issue. I can fix it by using =_PictureLookup(A2,B2) but then it returns #NAME?

          • Excel Off The Grid says:

            Hi Ted,

            I send you an e-mail response earlier today. I believe the two issues in your file are:
            (1) User Defined Functions must be included in a Module, rather than in the ThisWorkbook or a Sheet
            (2) The Function name must be unique (i.e. not the same as a Sub or another Function).

            Please let me know if that fixes the issue.

  2. Neil says:

    Hi, I went for the first option and got it working fine. Thanks!

    Say I have a spreadsheet where I want the same functionality 30 times… all looking at the same drop down list/pics. Do I have to create 30 rules? Or is there a slicker way of doing it? An example might be a list of 30 kids – I choose their favorite fruit next to each child, and want picture of the fruit next to each one. Thanks.

    • Excel Off The Grid says:

      Hi Neil,

      Unfortunately, the named range is applied to the picture and not a cell, therefore it is not possible to apply a relative cell reference. Which means by using the INDEX/MATCH method it would be necessary to create 30 named ranges and 30 pictures.

      With the VBA User Defined function it would be possible, as it behaves like a standard Excel formula.

  3. Peter says:

    Hello.
    Solution 3 works very well. Thank you!
    However, it seems that it can only display one picture.
    For example, if I use the function “=PictureLookup(E2,C2)”, I get the picture on C2.
    But if I add another line “=PictureLookup(E3,C3)”, I can’t get a second picture (until I delete the previous “=PictureLookup(E2,C2)” formula).

    Do you know if there is a solution to re-use the PictureLookup function (to display more than one picture)?

    Thanks!

    • Peter says:

      I think I found a solution:
      in the VBA Code (solution 3), delete the line lookupPicture.Delete

      This is great!!! Thanks again for the VBA solution

  4. LM says:

    Hi,
    Great article, thanks!
    I am trying the first option(the only one I basically understand).
    On my first worksheet, I have a dropdown list linked to a list of bottle names on a second worksheet. On this second worksheet, the bottles names are on the same rows as their pictures. I want the picture of the bottle selected with the dropdown list to appear dynamically on the first worksheet.
    Everything works fine until I get to the step where I have to select the pasted image and change the address in the formula bar to be equal to the named range created before.
    Excel then tells me that the “Reference is not valid”.
    Would you happen to know what could cause this and how I could fix it?
    Thank you!

    • Excel Off The Grid says:

      Hi LM,

      This occurs where the text entered into the formula bar is not equivalent to a valid cell address. The possible fixes are:
      – Check there is an = sign in the formula bar at the start of the Named Range
      – Check the spelling of the Named Range is correct
      – When creating the Named Range is the INDEX/MATCH set-up correctly and looking at the correct sheets?

      One of these should be the problem. Let me know how you get on.

  5. LM says:

    Hi!

    Thank you for your answer!

    I think that the problem is in the set-up of the formula for the named range, but I cannot find where it is. The formula for the named range is as follow:
    =INDEX(Table1[Picture];MATCH(‘Product Dashboard’!$D$2;Table1[Bottle Name];0))

    (I used “;” instead of “,”as in your example because excel wouldn’t accept the formula without it. If I understand correctly this comes with Excel 2013)

    Do you see anything wrong with it?

    Would you be ok to have a quick look at the file itself?
    I’m pretty sure that the mistake is obvious for someone who understand a little bit more excel.

    Thank you.

    • Excel Off The Grid says:

      I think I’ve solved it.

      The issue is with the single quotation marks surrounding the ‘Product Dashboard’ within the INDEX/MATCH function.

      They have been converted into a different type of quotation mark. Try deleting them and re-typing them. When I did that I managed to get it to work.

      Let me know if that solves it for you too.

  6. Kirsten says:

    Hey there, I am using the VBA option to have a picture populate from a file with pictures that match the value I am putting in. I only changed the location name for the file containing pics in the formula above, and have verified that they match the value I type in to get the picture to pop up in my selected cell. However, I am getting a #NAME error when trying to use the function in excel. Keep in mind I am a total noob :). I had to google how to create a module. Any ideas what I have done wrong? I can send screenshots.

    • Excel Off The Grid says:

      Hi Kirsten,

      The most likely issues to check are:
      (1) Is there a backslash ( \ ) at the end of file path?
      (2) Is the file type of the image a “.png”, if not you will need to change the code to include the file type you are using.

      If these still don’t fix your issue let me know.

Leave a Reply

Your email address will not be published. Required fields are marked *