We can use a data validation list or a Slicer to lookup pictures in Excel. This ability to lookup pictures is handy for returning images of products, employees, logos etc. The only limit is your imagination.
In this example I’m going to lookup pictures of the Tour de France stage profiles*.
*Source 2013 Tour de France.
Enter your email address below to download the sample workbook.
Watch the Video
Lookup Pictures in Excel – Setup
Step 1: Insert the images in a cell
Place each image in its own cell. It’s important that the cell containing the image is at least as big or slightly larger than the image itself. You can see in the screenshot below that the image fits perfectly inside of cell A1.
Important: If any part of your image sits outside of the cell it will be distorted when you look it up.
Step 2: Name the image cells
Next, give each cell that contains an image a unique name. You can see in the name box in the screenshot below that the cell is called ‘profile_1’:
To name a cell, click in the name box (shown in image above), type in the name and press ENTER.
Note: The cell names cannot contain any spaces.
Step 3: Supporting Data
Create a table that lists the images cell names and the name you want to use to lookup the image. You can see my table below (called Table1) contains the list of Stages and the Image Cell Names for each stage.
Tip: If your image name and lookup names are the same then you only need one column.
Step 4: Insert a Data Validation List or Slicer
Next you need to decide how you want your users to lookup the images. In the sample file I’ve included two options, one using a Data Validation List and another using a Slicer.
In the image below in cell B5 I have a data validation list, which is linked to the Stage column of the table shown in step 3.
The Stage name in cell B5 returned by the data validation list needs to be converted into the cell name for the image. I’ve used the XLOOKUP function in cell B6 to lookup the Table in step 3, but you could equally use VLOOKUP or INDEX & MATCH:
Tip: You can hide this formula on another sheet out of sight. I’ve included it here so you can see it in context.
Tip: Cut and paste the Slicer to the sheet where you want the pictures displayed.
As the Slicer selections are made, the PivotTable filters to show the Image Cell Name for the selected stage.
Step 5: Set Up the INDIRECT Named Formulas
Create a named formula using the INDIRECT function that references the cell containing the image cell names returned by the data validation list or the Slicer. In the screenshot below you can see the data validation method named formula is called profile_dv and for the Slicer it’s called profile_slicer:
The named formula, profile_dv, references cell B6 on the Lookup Images sheet. And named formula, profile_slicer, references the image cell name displayed in the PivotTable on sheet ‘Stages’ in cell G2.
Step 6: Insert the Image
Copy one of the images and place it on the worksheet where the data validation list or Slicer are that your user will be making their selections from.
With the image selected, click in the formula bar and type an equal sign followed by the named formula you set up in the previous step, then press ENTER:
That’s it, you can now lookup Pictures in Excel via the data validation list or Slicer, depending on which method you chose.