You can follow along using the example workbook (VLOOKUP pictures example).
A picture lookup example
In this example we’re going to have a set of data which represents the number of visitors driven by various traffic sources. We want to enter different volumes and automatically show icons of the traffic sources in the right order. Like this.
In our workbook we have two tabs called Pictures and Data. Data is where we’ll show the rankings (as above) and the Pictures tab is where we’ll keep the pictures, as follows:
In order to do this you need to follow these steps…
Step 1: Make the Pictures table
Make a list of the names of the traffic sources in the Pictures tab as above and resize the cells (widening the rows) so that they can fit a picture next to them. (Note, the pictures each need to fit within their respective cells and should be fairly similar in size)
The way I put the pictures into the table is by using the Windows snipping tool but you can also use SnagIt or simply copy and paste the picture from your internet browser and then resize so that they fit within their respective cells.
You need to name the cells according to the picture they contain. So to start with, click on cell B2 (being sure to click on the cell and not the picture) and name the cell as in the picture below by typing “GooglePlus” into the little box on the top left, where the arrow is pointing to.
Repeat this for the remaining picture cells.
Step 2: Name the cells which will call the pictures in the Data sheet
Let’s go to the data sheet and select the first cell that will call the picture by using its name. In our example this is cell E1.
We’re going to call the cell “Rank1” and we need to adjust its reference using the following steps:
Select the cell E4
Go to the Formulas>> Define Name and change the Refers To field to include the term Indirect (see below)
Do the same for your Rank2 and Rank3 cells (F4 and G4).
Step 3: Put the pictures into the data sheet
Copy any one of the picture cells from the Picture table created in Step 1. In this example we’re going to copy B3 (the cell containing the Facebook logo). Go to the Data sheet and paste special>> linked picture under Rank 1 (right click cell E7 and select the icon showed below)
Erm, hello? What’s the Facebook picture doing where the GooglePlus one should be? There’s one more thing you need to do. Select the picture and you’ll see that it references the cell where it came from. We need to change it so that it references the appropriately named picture cell as follows:
You’ve done it! All you need to do is repeat for the Rank 2 and Rank 3 pictures.
If you don’t want to see the words above the logos (e.g. you just want the Facebook logo and not the word “Facebook” above it) you can change the cell text colour to white and simply drag the logos up.
To watch them change, just mess around with the volumes in the table on the left of the Data sheet to make the rankings change.
So to recap, you named the picture cells with the logos in them, you named the cell that was to call the picture (e.g. Rank1) and then you pasted the picture and made it reference the Rank1 cell which returned the picture.
A little convoluted but once you do it a couple of times you get used to the process.
And remember, you can download this example from our tutorials and examples page.
Want to get good at using the VLOOKUP function?
Download our practical VLOOKUP tutorial workbook with step-by-step instructions and examples to help you become a VLOOKUP pro.