Excel : Picture Lookup Excel ( https://trumpexcel.com/ )

There are many Excel functions (such as VLOOKUP, LOOKUP, INDEX/MATCH) that can go and fetch a value from a list. Imagine doing the same, but instead of fetching a value, you can fetch a picture.
In this tutorial, I will show you how to do a picture lookup in Excel.
It’s simple yet make you look like an Excel Magician (all you need is this blog and sleight of hands on your keyboard).

Picture Lookup in Excel

I have a list of the 20 teams in English Premier league (ranking based on scores as of Nov 20, 2013) along with the club logo in the adjacent cell.
The intent is to select a club and the picture should change to display its logo.
Something as shown below:

Here are the steps to look up a picture in Excel:

1. Have the names of all the items (team names) in a column.
2. In the adjacent column, insert the picture for the item (club logo in this example).
3. Create a drop-down list with all the names of the clubs (I have the drop down list in E3).
4. Go to Formulas –> Define Name.

4. Create a Named Range ClubLogoLookup. To do this enter the following the New Name dialogue box:
   • Name: ClubLogoLookup
   • Refers to: =INDEX($C$3:$C$22,MATCH($E$3,$B$3:$B$22,0))

5. Go to any cell that contains the logo and press Control + C.
6. Click anywhere else on the sheet and then go to Home –> Paste –> Other Paste Options –> Linked Picture.

7. Click on the picture and you will notice a cell reference in the formula bar (for example =$C$3). Delete the reference and type =ClubLogoLookup

8. That’s it!! Change the club name from the drop down and it will change the picture accordingly

How it works:

I changed the reference of the picture to a defined name (ClubLogoLookup). 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).
Note: In this case, INDEX function returns a cell reference instead of a value. So the named range ClubLogoReturn stores a cell reference, which changes when the club name is changed.