But you can’t look-up images using these formulas. For example. 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. But that doesn’t mean it can’t be done. In this tutorial, I will show you how to do a picture lookup in Excel. 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). Click here to download the example file. Below is a video of the picture lookup technique (in case you prefer watching a video over reading).
Picture Lookup in Excel
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.
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. Something as shown below:
There are four parts to creating this picture lookup in Excel: Let’s go through these steps in detail now.
Getting the data ready
Make sure the logos fit nicely within the cell. You can resize the images so that these are within the cell, or you can expand the cells.
Creating the Drop-down list
The above steps would give you a drop-down list in cell E3.
Creating a Linked Picture
In this part, we create a linked picture using any of the existing images/logos. Here are the steps to create a linked picture: The above steps would give you a linked picture of the cell that you copied. This means that if any changes happen in the cell that you copied, it will also be reflected in the linked picture).
In the above image, since I copied the cell C3 and pasted a linked picture. Note that this is not connected to the drop down as of now. Also, when you paste the linked picture, it creates an image. So you can move it anywhere in the worksheet.
Creating a Named Range
Now we have everything in place, and the last step is to make sure that the linked picture updates when the selection is changed. As of now, the linked picture is linked to only one cell. We can connect it to the drop-down selection by using a named range. Here are the steps to do this: That’s it!! Change the club name from the drop-down and it will change the picture accordingly. How does this Picture Lookup Technique work? When we created a linked picture, it was referring to the original cell from which it was copied. We changed that reference with the named range. 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. For example, if I select Arsenal, it returns, C3 and when I select Chelsea, it returns C6. 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. For this trick to work, the defined name should return a cell reference only. This is achieved by using the combination of INDEX and MATCH functions. Here is the formula: =INDEX($C$3:$C$22,MATCH($E$3,$B$3:$B$22,0)). The MATCH part in the formula returns the position of the club name in the drop-down. For example, if it’s Arsenal, MATCH formula would return 1, if its Chelsea then 4. The INDEX function locates the cell reference that has the logo (based on the position returned by MATCH). Try it yourself.. Download the Example file from here
How to create an Excel Dashboard. Creating a KPI Dashboard in Excel. English Premier League Dashboard. How to Use VLOOKUP with Multiple Criteria. How to Save Excel Charts as Images
I did all the steps and when I look up I get a zero instead of the picture i have one concern ,i am trying to do image lookup in one excel file to another excel,but it was not coming .so kindly need your help on this. Can you please help me understand my error? I have a question. In this example the filter cell is E3 and the image is in G3.When i chose a team the image is changing. I want to do this filter in all th cell E:E.If i choose a team in E4 it changes the images in G4, if i choose a team in E5 it changes the imagine in E58,etc. Thank you very much. Yes this can be done. You will have to create multiple named ranges (ClubLogoLookup2, ClubLogoLookup3, and so on..). Just change the reference from $E$3 to $E$4 and so on. Here is a sample file – http://goo.gl/Cwk6ls Something like this: =INDEX(‘Tab1’!$J$7:$J$30,MATCH(‘Tab2′!$A$13,’Tab1’!$D$7:$D$30,0))
