This is achieved by using the combination of INDEX and MATCH functions. Since we have assigned the named range to the linked picture (by changing the reference to =ClubLogoLookup), it now refers to the new cell references, and hence returns an image of that cell.įor this trick to work, the defined name should return a cell reference only. For example, if I select Arsenal, it returns, C3 and when I select Chelsea, it returns C6. This named range is dependent on the drop-down and when we change the selection in the drop-down, it returns the reference of the cell next to the selected team’s name. We changed that reference with the named range. When we created a linked picture, it was referring to the original cell from which it was copied. How does this Picture Lookup Technique work? That’s it!! Change the club name from the drop-down and it will change the picture accordingly. Delete this cell reference and type =ClubLogoLookup. You will notice a cell reference in the formula bar (for example =$C$3). Select the linked image that we created in the previous step.In the New Name dialog box, make the following entries:.This will open the ‘New Name’ dialog box. We can connect it to the drop-down selection by using a named range. As of now, the linked picture is linked to only one cell.
Now we have everything in place, and the last step is to make sure that the linked picture updates when the selection is changed. So you can move it anywhere in the worksheet. Note that this is not connected to the drop down as of now.Īlso, when you paste the linked picture, it creates an image. In the above image, since I copied the cell C3 and pasted a linked picture. This means that if any changes happen in the cell that you copied, it will also be reflected in the linked picture). The above steps would give you a linked picture of the cell that you copied. Click on the Paste Linked Picture icon.Go to the Paste Special option and click on the small right-pointing arrow to get more options.Right-click on the cell where you want to get the linked picture (it can be any cell as we can adjust this later).Copy the cell (use Control + C or right-click and select copy).Make sure you have selected the cell, not the logo/image. Here are the steps to create a linked picture: In this part, we create a linked picture using any of the existing images/logos. The above steps would give you a drop-down list in cell E3. Select the range that has the club names (B3:B22 in this example).This will allow you to select the cells in which you have the list for the dropdown. In the Source field, click on the upward-pointing arrow icon.In the Data Validation dialog box, within the Settings tab, make sure List is selected in the Allow drop-down (if not selected already).Click on the Data Validation option (it’s in the data tools category).Select the cell in which you want the drop-down (E3 in this example).You can resize the images so that these are within the cell, or you can expand the cells. Make sure the logos fit nicely within the cell. In the adjacent column, insert the picture for the item (club logo in this example).
There are four parts to creating this picture lookup in Excel: Now what I want is to be able to select a team name from the drop-down, and the logo of that selected team should appear. I have a list of the 20 teams in English Premier league (arranged in an alphabetical order) along with the club logo in the adjacent cell. It’s simple yet it’ll make you look like an Excel Magician (all you need is this tutorial and sleight of hands-on your keyboard).īelow is a video of the picture lookup technique (in case you prefer watching a video over reading).
In this tutorial, I will show you how to do a picture lookup in Excel.
if I have a list of team names and their logos, and I want to look up the logo based on the name, I can’t do that using the inbuilt Excel function. There are many lookup functions in Excel (such as VLOOKUP, LOOKUP, INDEX/MATCH, XLOOKUP) that can go and fetch a value from a list.īut you can’t look-up images using these formulas.įor example.