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.