Showing a Picture Based on the Value of another Cell in Excel
In this walk-through ,I will show you how to use Excel to make a specific image show depending on a cells value, or by having a drop-down list
In this example, I want to show a list of products, and have an image of that product display when I select it.
This isn’t overly difficult, but does use some advanced techniques to make it work.
Add your pictures in Excel
First, you need to add your picture to Excel. You need to add them as a ‘list’, the name of the picture, and the picture itself in another column.
To add a picture, INSERT tab à Pictures, browse to your picture, and click INSERT. Resize and position the image as required
It’s very important that you ensure the pictures you add sits within the boundary of the cell – make the cell bigger as required.
Build the Lookup Formula
First, decide which cell will hold the value of the product you’re going to locate. This cell might have some validation based on the list you created so the user gets a list of values to select from, or if you’re familiar with using Form Controls, I linked a Drop Down form control to my list of values and did it that way. Whichever approach you take, you should end up with a cell containing the name of the product you’re trying to bring back.
In my example, I’m using cell C16
We now need to create a formula which will identify which cell the image is stored in. However, we’re going to create this as a Named Range, as we need to use it in a certain way later.
From the FORMULAS tab, open the Name Manager, and select ‘New’
Under ‘Name’, enter PRODUCT (or any unique name – no spaces). Under Refers To, you should modify and insert the following formula…
In hindsight, ‘PRODUCT’ isn’t the best name to use, as it’s also the name of a built-in function. Therefore, I would suggest PRODUCTIMG or something unique. Where ever you see PRODUCT, please read as PRODUCTIMG!
=INDIRECT(ADDRESS(Product_List_Row_Number-1+MATCH(Product_Selection, List_Of_Picture_Names, 0), Column_With_Pictures))
The Product_List_Row_Number should be the row number of your first product.
is the cell containing the item you want to match (eg, C16 in this example)
List_Of_Picture_Names is your list of product names
Column_With_Pictures is the column number containing your images. If your images are in column C, you’d enter 3
My formula looks like this…
As pointed out by Dandy in the comments, if you plan to keep your images in a separate tab, you will need to update your INDIRECT formula to include a reference to the worksheet which contains your images (Product List in my example)
Therefore, your formula to use above would look like
=INDIRECT("'Product List'!"&ADDRESS(2-1+MATCH('Product List'!$R$8,'Product List'!$B$2:$B$4,0),3))
Click OK to close the New Name window, and Close on the Name Manager
Bring it all together
Now, copy any of your images – doesn’t matter which, and paste it where you want your dynamic image to appear
Now, select that image again. In the formula bar, which should currently to blank, enter ‘=PRODUCT’ (or whatever you named your formula in the previous step)
And that’s it – when you change your product, the image should change with it.
If your product changes, but isn’t quite bringing back the right image, tweak your names formula. Ensure the range you selected for the List_Of_Picture_Values begins on the same row as your Product_List_Row_Number, and ensure the column number is right
You can then build all your other information around this, as I have done.