3 ways to change or insert images based cell values

Change image based on cell value - Featured image

From stock parts to holiday villas, from employees to logos, there are plenty of reasons to insert and automatically change an image based on a cell.  You may think of this as a lookup function that returns a picture, rather than a value.  Ultimately, the purpose is to link an image or picture to a cell; change the cell value and the picture changes.

INDEX MATCH method to change picture based on cell

In this post, we’ll explore three different methods to achieve this task.  Each solution has it’s own strengths and weaknesses; learn them all and pick the one which best meets your requirements.

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

Download the example file
I recommend you download the files which support this post, as you’ll be able to work along with examples.  This is the best way to learn.  You’ll be able to see the solutions in action, plus the file will be useful for future reference.  The support files are available for FREE to newsletter subscribers.

Click below to subscribe and gain access to the subscriber area.  You will also receive:

  • My favorite tips and tricks direct to your inbox
  • Exclusive content (which is only available to subscribers)
  • FREE tools and downloads

Download Icon

If you’re already a subscriber, click here to log-in to the subscriber downloads area.

The filename for this post is 0004 Change image based on cell value.zip.

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

If you’re working along with the example file, we’ll begin with the Linked Picture tab.

As an overview, this method works as follows:

  1. Create a dynamic named range using the INDEX MATCH formula combination
  2. Use the named range as the source for a linked picture

Now let’s look at each of these steps in turn.

The example data contains a list of countries in Column A and their relevant flags in Column B.

Start scenario - list with flags

Create a dynamic named range with INDEX MATCH

Start by entering one of the country names in a cell (for our example, use cell D2).

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

Start scenario - list with flags

In the New Name dialog box, create a new named range called CountryLookup.

Rather than a range, the Refers to box needs to contain a formula calculates to a range.  We will use INDEX/MATCH for our example.

CountryLookup named range

The formula in the Refers to box is:

=INDEX('Linked Picture'!$B$2:$B$11,
MATCH('Linked Picture'!$D$2,'Linked Picture'!$A$2:$A$11,0))

In this formula:

  • Cells B2-B11 contain the flags
  • Cell D2 contains the name of the country to be matched
  • Cells A2-A11 contains the names of the countries

Click OK to close the New Name dialog box.

Using a named range as the source for a linked picture

Now that the named range has been created, follow the following steps to create a linked the picture.

  1. Select any cell which contains a flag.
  2. Click Home -> Copy (or Ctrl + C) to copy the cell.
  3. Select a different cell (use E2 if working with the example file).
  4. Click Home -> Paste (drop-down) -> Linked Picture (alternatively, the Camera Tool is an option).
    Paste special - linked image from ribbon
  5. The pasted image will appear.
  6. Select the pasted image.
  7. Change the address in the formula bar to be equal to the named range we created earlier (CountryLookup in our example).
    Linked picture to named range

Change the name of the country in cell D2 and watch the image change.  For completeness, change cell D2 into a data validation drop-down list containing all the countries.

INDEX MATCH method to change picture based on cell

The “Reference isn’t valid” Error

Reference isn't valid error message

If you receive the Reference isn’t valid error there are two likely problems:

  1. The result of the INDEX/MATCH function produces an error.  This usually occurs where the name of the country is not identical to one of the countries in the list.
  2. The named range applied to the picture does not exist.

Other formula options

INDEX/MATCH is a formula combination, which can achieve some amazing things; picture formulas are just one of those amazing things.  But any formula which returns a range will work inside the named range.  Some easy alternatives are:

XLOOKUP (new function available in Excel 365)

=XLOOKUP('Linked Picture'!$D$2,'Linked Picture'!$A$2:$A$11,
'Linked Picture'!$B$2:$B$11)

Find out more about the XLOOKUP function in this article: XLOOKUP function (support.office.com)

OFFSET / MATCH

=OFFSET('Linked Picture'!$A$1,MATCH('Linked Picture'!$D$2,
'Linked Picture'!$A$2:$A$11,0),1)

To learn about other formulas that return ranges, check out this post.

Change image with chart fill + #N/A

If you’re working along with the example file, check out the Chart Fill tab.

As an overview, this method works as follows:

  1. Create a formula that calculates 1 for the selected country or #N/A for the other countries.
  2. Create a stacked column chart with the country list and formula created in 1 above as the source
  3. Add the images as the fill for each chart series

Here are those steps in more detail

Formula to calculate as 1 or #N/A

Enter a country name in a cell (for our example, use cell D2).

Columns A contains the names of countries.

VLOOKUP to force NA error

Enter the following formula in cell B2:

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

The purpose of the formula is to calculate 1 if cell D2 is the same as the country in column A; otherwise, #N/A is displayed.  Copy the formula down to the last country name in the list.

Create a stacked column chart

Now it’s time for the chart trickery:

  1. Select the list of country names and the formula result (cells A2 to B11 in our example).
  2. From the ribbon, click Insert -> Charts -> Stacked ColumnInsert stacked column chart
  3. A new chart will appear on the worksheet.
  4. Right-click on the series, then click Select Data… from the menu.
    Right-click - Select Data
  5. The Select Data Source dialog box will open.
  6. Click the Switch Row/Column button, the Legend Entries box on the left should now contain the list of countries as different chart series.
    Switch row and columns in Select Data Source
  7. Click OK to close the Select Data Source dialog box.
  8. Right-click on the data bar and select Format Data Series… from the menu.
    Right-click - Format Data Series
  9. Change the Gap Width to 0%
    Change gap width
  10. Right-click on the vertical axis and select Format Axis… from the menu.
    Right-click axis - Format Axis
  11. Set the axis maximum equal to 1.
    Change max axis value
  12. Select the Chart Title, press the delete key.
  13. Next, select the vertical axis and delete it.
  14. Then, select the horizontal axis and delete that also.
  15. Re-position and resize the chart to fit your requirements.  The worksheet will look similar to this:
    Worksheet including resized chart
  16. Change the selected country to be the first country in the list.
  17. Right-click on the chart fill and select Format Data Series… from the menu.
  18. Change the fill to Picture or texture fill and click Insert…
    Picture Fill - Insert Button
  19. Navigate to the picture location and click Insert.
  20. Repeat steps 16 to 19 for each country in the list.

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

Chart method to change picture from cell

Inserting images one at a time, in this way, can be time-consuming.  So, I have created a macro that can automate this for you.

Add the chart fill automatically with a macro

The macro below inserts pictures into the chart fill automatically.  To work, the names of the countries must exactly match the names of the pictures.

To run the macro for your scenario:

  1. Change “Chart  1” to the name of your chart.
  2. Set the file path to the folder containing the images, and change the file extension.
  3. In the worksheet, select the cells containing the country names and run the macro.  The chart should automatically populate with the relevant 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

Generate accurate VBA code in seconds with AutoMacro

AutoMacroExample

AutoMacro is a powerful VBA code generator that comes loaded with an extensive code library and many other time-saving tools and utilities.

Whether you’re an experienced coder looking to save time, or a newbie just trying to get things to work, AutoMacro is the tool for you.

Change image with a VBA User-Defined Function

If you’re working along with the example file, look at the UDF tab.  Finally, we will consider a VBA user-defined function.  This is a formula created using VBA macros to insert and change pictures.  Learn more about UDF’s here.

WARNING! – User Defined Functions that change objects are not officially supported in Excel.  While this works in the Windows version of Excel, I’ve not successfully made it work on the Mac version.

Other VBA options are available, but they are not as flexible as the UDF method presented here.

The VBA code

Copy the following code into a standard module within the Visual Basic Editor.

Public Function PictureLookupUDF(FilePath As String, Location As Range, Index As Integer)

Dim lookupPicture As Shape
Dim sheetName As String
Dim pictureName As String

pictureName = "PictureLookupUDF"

sheetName = Location.Parent.Name

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

'Add the picture in the right location
Set lookupPicture = Sheets(sheetName).Shapes.AddPicture _
(FilePath, msoFalse, msoTrue, Location.Left, Location.Top, -1, -1)

'Resize picture to best fit the range
If Location.Width / Location.Height > lookupPicture.Width / lookupPicture.Height Then
    lookupPicture.Height = Location.Height
Else
    lookupPicture.Width = Location.Width
End If

'change the picture name
lookupPicture.Name = pictureName & Index

PictureLookupUDF = "Picture Lookup: " & lookupPicture.Name

End Function

Using the UDF on the worksheet

The user defined function can now be used on the worksheet just like a normal function.

The PictureLookupUDF uses the following arguments:

=PictureLookupUDF(FilePath, Location, Index)
  • FilePath = Full file path to the image, including the file extension.
  • Location = Range of cells where the image should be placed.
  • Index = A unique reference number to identify the image.

The formula is used in the example below.

Using the Picture Lookup UDF

In cell D6 the formula is:

=PictureLookupUDF(D2&C6&D4,D6:D12,1)
  • D2&C6&D4 – consolidates the folder path, country name, and file extension
  • D6:D12 – the range of cells of where the place the picture
  • 1 – the unique reference number to identify the image
WARNING! – Make sure the folder path contains a slash before the file name.

Images can be placed on a different worksheet, by using a standard sheet reference:

'Sheet 1'!$D$6:$D$12

Look at this function in action.  It behaves just like the previous examples.

PictureLookupUDF function in action

The Index argument can be used to insert multiple images.  For example, to include a second image, increase the index number by 1, as shown below.

=PictureLookupUDF(D2&C16&D4,D17:D23,2)

If you wish to amend the VBA code to change the images further, such as size, rotation, etc., you can find further guidance here.

Advantages & disadvantages of each option

Named range + INDEX/MATCH + linked picture

Advantages:

  • All the images are contained on the worksheet

Disadvantages:

  • Could accidentally move images outside of the cell range
  • Takes a lot of preparation to use multiple images

Chart fill + #N/A

Advantages:

  • Images contained within the chart, so difficult to change images in error

Disadvantages:

  • Time-consuming to set-up
  • Requires a helper column to function correctly
  • Takes a lot of preparation to use multiple images

VBA User Defined Function

Advantages:

  • No additional columns required on the worksheet
  • Easy to use with multiple images

Disadvantages:

  • Technique not fully supported by Microsoft
  • Requires users ability to use VBA
  • Image name must be the same as the value on the worksheet
  • Difficult to share the workbook with others who don’t have access to the image folder

Conclusion

There are many ways to change an image based on a cell value.  Only one VBA option has been presented in this post, but there are many other possible solutions.

Which method should you use?  It really depends on your circumstances.  My advice is to learn them all and apply what you think works best in each scenario.

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

10 thoughts on “3 ways to change or insert images based cell values

  1. Johanna Sirén says:

    Hi! Awesome functions, I got finally my template to work as intended. But I do have still one issue:

    What if I would need the function “Index/Match” to perform it’s action (meaning “fetch the picture to cell E2” in your example sheet) ONLY IF there is a country added into the cell D2? What I mean is that IF cell D2 is empty, the function “shouldn’t do anything” in cell E2?

    Now I basically have solved this issue with extra value on the Linked Picture list saying “No country” and then I added as the flag pic just an empty white box. So if you choose “No country” in cell D2, it fetches the white box into cell E2. I am fine with this but there is a risk if someone decides to use some other colored paper than white so I am already seeing the complaints that what are the boxes 🙂

    Thank you so much already in advance if you’re able to help me here!

    • Excel Off The Grid says:

      Hi Johanna,

      You could try wrapping the INDEX/MATCH function in an IFERROR function. I think IFERROR was introduced in Excel 2007, so should be safe to use now.

      Using the example file, the formula would be as follows:

      =IFERROR(INDEX('Linked Picture'!$B$2:$B$11,MATCH('Linked Picture'!$D$2,'Linked Picture'!$A$2:$A$11,0)),'Linked Picture'!$C$2)
      

      The last reference of ‘Linked Picture’!$C$2, just has to refer to any blank cell.

      It should work because, if a lookup is not found a #N/A error is returned by the INDEX/MATCH, this will trigger the IFERROR and point to Cell C2. Hopefully that makes sense.

      Hopefully that helps.

      Thanks,

      Mark

  2. Said says:

    Hi all,
    I tried this function on a protected sheet but it didn’t work even if added a code to unprotect the sheet at the beginning of the function (UnprotectSheet Sheets(“Sheet1”)).
    Is there a way to fix this issue .

    Thanks.

    • Excel Off The Grid says:

      Unfortunately not. UDF’s don’t mix well with protected sheets… sorry.

      The other methods appear to work OK on protected sheets.

  3. Ryno says:

    Hi,

    Thank you for this code… its Awesome!

    How do I go about if I want to insert the image to the center of the selected range of cells and not to the Left Top?

    Thanks!

    • Excel Off The Grid says:

      You just need the width and height of both the image and the range of cells.

      As an example, to put an image in the centre of cell B2 – H8 you would use:

      ActiveSheet.Shapes("myPic").Left = ActiveSheet.Range("B2").Left + _
          ActiveSheet.Range("B2:H8").Width / 2 - _
          ActiveSheet.Shapes("myPic").Width / 2
      
      ActiveSheet.Shapes("myPic").Top = ActiveSheet.Range("B2").Top + _
          ActiveSheet.Range("B2:H8").Height / 2 - _
          ActiveSheet.Shapes("myPic").Height / 2
  4. AN says:

    hi there,
    Thanks so much for your helpful description and files.

    Just a small comment concerning the VBA-chapter. I experienced some issues using commas instead of semicolons when separating the values in the formula.

    You wrote the following code:
    =PictureLookupUDF(D2&C6&D4,D6:D12,1)
    Shouldn’t it be this instead:
    =PictureLookupUDF(D2&C6&D4;D6:D12;1)

    • Excel Off The Grid says:

      The use of commas or semi-colons is dependant on your regional/language settings in Excel. Use the argument separator you would normally use for standard functions.

  5. Elad Mezrahi says:

    the picturelookupUDF works great, but i have one problem. the unique identifier. Is there a way to have it change on its own? IE: when i copy the following formula “=PictureLookupUDF($E$2&A2&$E$3,B2,1)” 1 cell down, i would like it to change to “=PictureLookupUDF($E$2&A3&$E$3,B3,2)”

Leave a Reply

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