How to show image linked to the list element

  • Excel
  • 2016
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):

Defined Names 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 this 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:
    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:
    Clipboard paste list options in Excel 2016
  • Picture:
    Clipboard paste list options 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 is sorted
  • FALSE, if items should be 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

Thank you for visiting OfficeToolTips

We are glad to help you in your work. However, you are using ad blocker and our efforts will not be rewarded.

Please consider disabling ad blocker before continuing the reading.