Dynamic Picture Lookup in Excel

Just 5 easy steps – No VBA

How can you lookup different pictures based on the value of a cell?

Just to give you a heads up. This is a VBA-free tutorial!

We saw the dynamic picture lookup in action towards the end of this video, right here.

You can apply this concept to anything where you need dynamic pictures.

For example:

  1. Company logos
  2. Employee pictures
  3. Flags
  4. Houses
  5. Report headers…

Basically anywhere where you’d like the picture to change once the value of a cell changes.

Excel lookup multiple pictures dynamic formula no vba

Here’s our Picture Lookup Challenge

We’d like to select the country from our drop-down list,

and we’d like the image – i.e. the flag for the country to reflect our selection:

Excel_How_Lookup_dynamic_picture_referencing

Let’s get started…

Step 1: Organize the pictures in separate cells

Make sure each single image is “inside” its own cell. It should be surrounded by the cell borders. You’ll see why in a bit…

Adjust the row height so your images have enough space. To make sure each image is inside its own cell, you can select the images –  Shortcut key Control + A selects all images (make sure you click on an image first before you use the shortcut key, otherwise you select all the cells instead).

Now use the options inside picture tools to align the images properly. You can for example, align them to the left side and distribute them vertically. For proper vertical distribution, make sure the first and the last image sit correctly in their cells. The rest will be proportionally distributed.

Excel lookup pictures put images in cells

Now for the data validation:

Go the cell where you’d like your drop-down to be. Then go to:

Data / Data Tools / Data Validation

Select List from the Settings tab. For data source, select the range where you have your list. In my example that’s:

=Master!$B$3:$B$8

Excel data validation drop down list

Step 2: Assign Names to cells

Go back to your images. Each cell with an image needs a name. It’s easiest to assign the text you use for your list as the cell name.

For example cell A3 should get the name “Germany”, A4: “France” and so on.

You can do this the slow way, by selecting each cell and then typing the name inside the name box. Or you can do it the fast way:

Highlight the image cells and your names –  in this example A3:B8. Go to:

Formulas / Defined Names / Create from Selection – select right column.

Name manager Excel selection from range

The names are created automatically – so now the text inside B3 is the name of the cell for A3.

Think of names as bookmarks. You have now book marked cell A3 to be “Germany”. Every time you type in Germany inside the name box, you jump to cell A3 on the Master tab.

Step 3: Copy one of the pictures and make it the placeholder image

Now click on one of the flags (doesn’t matter which one) and copy it.

Go to the place you’d like to have your dynamic image and paste it there. This is now your placeholder image. It’s not dynamic yet, but it’ll be soon.

Step 4: Assign a name to INDIRECT formula

The INDIRECT formula is the perfect formula to handle this step.

Why?

Because INDIRECT, indirectly gives you the right cell address.

What does that mean?

Whichever cell reference you give to INDIRECT, it tries to translate the text inside the cell to an address. If you type A6 inside cell B2 and write a formula like this:

=INDIRECT(B2)

You get whatever is inside cell A6.

Why?

Because INDIRECT uses the text it sees inside cell B2 as an address – i.e. as the new cell reference. Go to cell A6 and type in any text. Now your INDIRECT formula which is referencing B2, is returning what is inside A6. This can be a bit confusing…

Find out more about INDIRECT in this post. You need to think a little outside the box here. Just make sure you’re in the right mood.

Now let’s finalize Step 4. Go to Name Manager, click on new and type in this formula:

=INDIRECT(Report!$C$2)

It should reference the cell where you have your data validation list. Give it a name. In this example I called my formula “Flag”.

Excel INDIRECT Name manager

Step 5: Link this name to the placeholder image

Now comes the last step:

Use this new name as a link for your placeholder image:

Click on the image first – then immediately go to the formula box and type in

=Flag

(The name you used in name manager for your INDIRECT formula)

Excel Link Picture with formula

That’s it!

Test it out now. Select another category from your drop-down list and watch your image change!

That’s magic done in Excel.

Optimizing the image

Remember: You can use your pictures tools options to optimize the image.

You can resize it, crop it, add a frame to it;

Your options are limited to your imagination (and to picture edit tools)….

In the example shown, I’ve resized it and then cropped out the cell borders from the image.

Video and Workbook

Feel free to Download the Workbook HERE.

Free Excel Download

Unlock Excel VBA & Macros Course is here.

Save time. Achieve more.

Over 50 Excel macro examples for download & useful VBA codes you can use for your work.

Learn the WHY not just the HOW

LEARN MORE

24 Comments

  1. Michael June 13, 2018 at 11:50 am - Reply

    I keep getting a “Reference Isn’t Valid” error in the last step.

    • Leila Gharani June 14, 2018 at 12:45 pm - Reply

      Make sure your data validation drop down list is identical to the names you’ve defined…

  2. Charlotte Beesley June 13, 2018 at 11:30 pm - Reply

    Would this also work with a file with 2,000 images?

    • Leila Gharani June 14, 2018 at 12:44 pm - Reply

      I haven’t tested it. I would say yes -but might get quite slow…

  3. Shadrack Awunyo June 15, 2018 at 7:58 am - Reply

    Leila at it again. Awesome. I tried and it works. I even added Ghana my home country to the list!!!

    • Leila Gharani June 18, 2018 at 9:24 am - Reply

      That’s great!

  4. Mark June 22, 2018 at 3:03 pm - Reply

    Magic is right, this is awesome and very useful
    (currently working through Leila’s excellent ‘Unlock Excel VBA and Excel Macros’ on Udemy and got sidetracked…)

  5. khadim hussain June 22, 2018 at 3:35 pm - Reply

    can i get dashboard training from you,

  6. Jon Austin September 23, 2018 at 1:34 pm - Reply

    when my dynamic image loads it moves location and orientation

  7. Jon Austin September 23, 2018 at 1:37 pm - Reply

    When my file updates with the new image, it moves locations and orientation. I’ve tried to lock the size and move with cell but it continues to throw the image out of proportion to the cell.

  8. Mohamed December 29, 2018 at 12:15 pm - Reply

    Leila Gharan has to be thanked.

  9. Daniel January 2, 2019 at 7:56 pm - Reply

    does it work in excel online? Or is there an alternative to do so?

    • Leila Gharani January 3, 2019 at 12:04 pm - Reply

      I haven’t tested it on Excel Online yet – but as these are just formulas and data validation, I think it should work….

  10. sohail aaqil January 16, 2019 at 3:44 pm - Reply

    its brilliant

  11. Lo January 19, 2019 at 3:46 pm - Reply

    Is there a shortcut or quicker way to rename the images from Name Manager instead of one by one as I need to lookup images for a number of lists?

    • Leila Gharani January 20, 2019 at 6:28 pm - Reply

      Yes – from the Formulas tab, under defined names – select “Create from Selection” – before you click on it, select the range which should also include the names. You need to specify which column or row the names are and it creates all the names in one go.

  12. Kaveh Afshari February 8, 2019 at 11:37 pm - Reply

    Thank you for all the great videos.
    I am trying to use this process for company logos, however how can we accommodate spaces?
    Company names may have spaces, and the lookup (INDIRECT) will be referencing the NAMEs. And excel Names dont allow for any space.

    Cheers

    • Leila Gharani February 9, 2019 at 6:10 pm - Reply

      You can use indirect with the substitute function. So for the Names in name manager you would add an underscore “_” instead of the space. Then you use the substitute function to replace the “_” with space in the formula.

      • Kaveh Afshari February 11, 2019 at 7:10 pm - Reply

        Thank you. that worked great !

  13. Mark February 19, 2019 at 2:01 pm - Reply

    One of my value doesnt display the corresponding image..help

    • Leila Gharani February 20, 2019 at 5:05 am - Reply

      Make sure the name is identical to the names in the drop-down…

  14. Rajarshi Basu June 12, 2019 at 1:43 pm - Reply

    In the place of flags,can i put in some shapes (free form) and make it dynamic.

    • Leila Gharani July 4, 2019 at 6:50 am - Reply

      Yes you can. you also put in a chart as long as you fit in in the cell – or make the cell fit the object.

  15. Miguel September 16, 2019 at 3:28 am - Reply

    Leila, I have found very useful this piece of knowledge you have shared. I am very thankful!

    Also, I am amazed on how you take the time to respond to all these queries that people ask.

    I am finding very useful the tutorials and resources. It is really great!

Leave A Comment

Share This