Tutorial: How to use a VLOOKUP to return a picture

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.

example vlookup returning a picture

 

 

 

 

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:

vlookup example picture tab

 

 

 

 

 

 

 

 

 

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.

vlookup picture example name cell

 

 

 

 

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.

picture example

 

 

 

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)

rename cell

 

 

 

 

 

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)

paste linked picture

 

 

 

 

 

 

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:

reference ranking cell

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.

2 thoughts on “Tutorial: How to use a VLOOKUP to return a picture

  1. Susan

    When I try to do the last step, “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…” I get an error that says “Reference is not valid.” Can you help?

    Reply
    1. half-man-half-spreadsheet Post author

      Hi Susan,

      I’d suggest repeating Step 2 as the error suggests that it’s pointing to a cell that hasn’t been named correctly.

      Let me know if this helps. We’re happy to have a look at the file you’re working on if you need us to.

      Cheers

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *