fbpx

Image Lookup – How-to show dynamic picture in a cell [Excel Trick]

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Do you ever want to have an image or picture lookup in Excel? Something like below.

Demo of Excel Image Lookup - Search and display matching image

In this article, learn how to set up an image lookup using Excel. You can use this to display staff details, product images or machine parts etc.

Video - Dynamic Image Lookups in Excel

If you want to understand the picture lookup technique and see it in action, check out my YouTube video below. For text instructions and images, please read on.

Step 1: Set up your data

To get image lookups, you need to set up your data, ideally in a separate worksheet. The trick is to have one image per cell. Something like this:

image lookup - sample data

Tip: Do not use Tables for storing your data. Instead keep them in a spreadsheet range.

Your images need to fit snugly inside the cell, without touching the boundaries for best results.

Also make sure to turn off gridlines on that spreadsheet tab.

You can do this from View ribbon, as depicted below.

disable gridlines for image lookups

Step 2: Write formula to lookup the image

In a separate worksheet, we will write formula to lookup images. Let’s say we have an input cell with employee name in C5.

We need to get the matching employee picture.

The trick is to write a formula that returns a reference to the image of employee in C5.

You can use either INDEX+MATCH or XLOOKUP

Let’s use XLOOKUP:

=XLOOKUP(C5, data!$D$3:$D$14, data!$C$3:$C$14) 

This will return a reference to the cell with the image of employee in C5.

Of course the result of the formula would be 0, as column C (data!$C$3:$C$14) has no values (just pictures) in it.

Step 3: Insert a named range with the formula

Now that we know what formula to write, go to Formula ribbon and click on Define name button. 

  • Name will be “employee_picture”
  • Refers to will be our XLOOKUP formula from above

See below illustration to understand how the name can be created.

Step 4: Set up a picture link to show image lookup result

This is the last step folks. Go to the data worksheet where you have all the pictures.

  1. Select any one cell with image and copy it (CTRL+C).
  2. Go back to the results worksheet, right click anywhere and select linked picture from Paste Special options. See below picture to understand.

linked picture (camera tool) option in Excel paste special menu

 

  1. You will get a picture of the selected employee. But this one is special. It is linked to the cell you copied. 
  2. Select the picture and formula bar will show the address of the cell to which it refers.
  3. Now comes the most important step. Go to formula bar and edit the reference. Type =employee_picture and press enter.

Bingo, you have just created a picture lookup. 

If you change the input cell and type a different person name, the lookup image will show that person.

Related: Learn more about Picture Links

Tips on using Image Lookup Technique

Image lookup through picture links offers exciting possibilities. Here are some tips & gotchas you want to keep in mind.

  • The picture link images are just like any other images in Excel. So when you select them, you can use picture format ribbon to apply various formatting options to them. For example, you can crop them to shape or apply shadow effect to them.
  • Picture links work best if all images are of same size. 
  • While picture links look great on screen, they tend to be grainy when printed.
  • They work well with dashboards too. For example, You can use picture links to display top 3 products or bottom 5 sales persons in dashboards.
  • For image lookup scenarios, consider adding a missing person image and use that in XLOOKUP if_not_found parameter. It looks amazing.

⬇ Download Picture Lookup Example File

Click here to download sample file with the picture lookup example. It has few different XLOOKUP formulas, an additional image lookup trick to show team members and more. Examine the named ranges to learn how it works.

Got questions about image lookups? Fire away...

Give picture lookups a try and if you face any issues during implementation, please post a comment here so I can help you.

Also, check out these other types of lookups:

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

sorting-excel-data-in-multiple-levels

Sorting values in Olympic Medal Table style [Quick Tip]

It is Olympic season. Everyone I know is tracking the games and checking their country’s performance. One thing that we notice when looking at medal tally is,

A single Gold medal is worth more than any number of Silver medals. Like wise, a single Silver medal is worth more than any number of Bronze medals.

So, when you look at the ranking of countries, you see countries with single Gold medal higher up than countries with lots of Silver and Bronze medals (but no Gold).

6 Responses to “Image Lookup – How-to show dynamic picture in a cell [Excel Trick]”

  1. Karthik says:

    Tried with Index, Match.. Doesnt work out for me 🙁
    Plz help

    =INDEX(B2:H14,MATCH(A2,D2:D14,0),2)

  2. dlcartin says:

    I can't get this to work when 2 (or more) people have the same (1st) name. How do I fix this? BTW, thank you so much for the time & effort that you put into your examples!

  3. dlcartin says:

    Howdy,
    How do I use the 'employee_pic' on a different sheet within the same workbook. I'm also usning your 'Birthday Reminder' sheet and would like to show a pic of the current birthday person (should there be a pic of them in there, of course). Thanks for the amazing templates that you offer on your website...and, also, for any help that you render.

  4. Megan Fairhurst says:

    This is great when you only have 1 image to show but I need multiple rows of images and due to the cell referencing I would have to go in and create individual named references which would take just as long as copy & pasting the images in. Is there anyway to do this without locking to just one cell?

  5. Stephan Chayer says:

    i'm using 365, but really spinning 360. Can't seem to be able to name the range and assign a formula or unable to link these to the picture. Says "Reference isn't valid. The file was started in previous version Excel and seemed to work.

Leave a Reply