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

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

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:

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.

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.

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

You will get a picture of the selected employee. But this one is special. It is linked to the cell you copied.
Select the picture and formula bar will show the address of the cell to which it refers.
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.