How to show image linked to the list element

Excel 365
Microsoft Excel proposes many 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 images, text, hyperlinks, 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.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.