How to show image linked to the list element

Excel 365
Microsoft Excel proposes a lot of different features for creating a perfect presentation of your data. One of these features is linked elements. This tip shows how to create a drop-down list and automatically show linked objects, such as image, text, hyperlink, etc. for the selected drop-down list item.

For example, if you want to show the linked image and text for the selected item:

List with images in Excel 2016

To create a drop-down list with linked images, do the following:

   I.   Create a drop-down list from your data (see Creating a drop-down list in a cell for more details):

Data Validation in Excel 2016

In this example the drop-down list was created in the List sheet:

Drop-down list in Excel 2016

   II.   To show the linked picture of the selected item, do the following:

   1.   Create the name range of pictures:

   1.1.   On the Formulas tab, in the Defined Names group, click Define Name (see Creating and using named ranges for more details):

Define Name in Excel 2016

   1.2.   In the New Name dialog box:

New Name dialog box in Excel 2016
  • In the Name field, enter the name of this new range, for example, Pictures,
  • In the Refers to field, enter the formula:

= INDEX (<list of pictures>, MATCH (<selected item>, <list of items>, 0))

In this example:

= INDEX (Data!$C$3:$C$5, MATCH (List!$B$3, Data!$B$3:$B$5, 0))

   2.   Select any picture and copy it by doing one of the following:

  • On the Home tab, in the Clipboard group, click the Copy button:
    Copy to Clipboard in Excel 2016
  • Right-click in the selection and choose Copy in the popup menu,
  • Click Ctrl+C.

   3.   Select the cell where you want to show the linked picture.

   4.   On the Home tab, in the Clipboard group, click on the Paste list and then choose one of the options:

  • Keep Source Formatting:
    Paste with keeping source formatting in Excel 2016
  • Picture:
    Paste Special as Picture in Excel 2016

   5.   Select the pasted picture and in the Formula Bar type the name of your range (in this example, Pictures):

Linked image in Excel 2016

   III.   To show the linked text of the selected item, do the following:

On the appropriate cell, where you want to see the linked text, enter the following formula:

= VLOOKUP (<selected item>, <data table>, <column to show>, <table option>)

Where <table option> should be:

  • TRUE, if items in your table are sorted,
  • FALSE, if items should find an exact match.

In this example:

= VLOOKUP (B3, Data!$B$3:$D$5, 3, FALSE)

Drop-down list with linked images in Excel 2016

See also this tip in French: Comment afficher l’image liée à l’élément de liste.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more