Picture Lookup in Excel

Until now, Excel doesn’t have a lookup function that is designed to return a picture or pictures. That means there is no straight forward method for Picture Lookup in Excel. In this Excel tip tutorial, I will show you how to do Picture Lookup using the existing features in Excel.

I have the list of a few Marvel Superheros and their pictures in an Excel Worksheet. Now, I need an arrangement where I can select a Superhero from a drop-down list and the picture corresponding to that Superhero will appear on the adjacent side.

First of all, we need to create a ‘Drop-down’ list using the Superhero names.

We have the names of Superheros listed in the cells, B3:B7. To create a Drop-down list in the cell F2,

select the cell F2 > go to the Data tab of the Excel ribbon > Data Validation

Data Validation dialog will be activated. Select List from Drop-down menu under ‘Allow:’. In the input box for ‘Source:’, specify the address of the cells containing the names of the Superheros and click on OK.

Now, we have a Drop-down menu in the cell F2.

Next step is to create a Linked Picture. A Linked picture is a live image of a cell or a range of cells in a worksheet.

To create a linked picture of the cell C3, which contains the image of Iron Man

Right-click on the cell C3 > Copy

Right-click on the cell where you want to place the picture > Paste Special… > Linked Picture (I)

We now have the linked picture of the cell C3, in other words a live image of the cell C3. This picture can moved or resized using the Move and Control handles.

When I change the formula of the linked picture to =$C$5, we will have the live image of the cell C5.

Next step is connecting the Drop-down list and the linked picture using a ‘Lookup formula’ and a ‘Name’.

Go to the Formulas tab of the Excel ribbon > Define Name

In the input box for Name, type in SuperHero.

In the input box for Refers to:, type in the following formula and Click OK.

=INDEX(Marvel!$B$3:$C$7,MATCH(Marvel!$F$2,Marvel!$B$3:$B$7,0),2)

The above formula is a simple INDEX+MATCH formula to return a match from the second column of the data range B3:C7, according to the lookup value in the cell F2.

Right now, the linked picture is displaying the picture in the cell C5.

Select Captain America from the Drop-down list for Superheros.

Click on the Linked Picture. Go to the Formula bar and replace C5 with the Name, SuperHero.

As we have selected Captain America from Drop-down menu, the linked picture will display the picture of Captain America. Change the Superhero name and the linked picture will update.