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.
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:
15 Responses to “Image Lookup – How-to show dynamic picture in a cell [Excel Trick]”
Tried with Index, Match.. Doesnt work out for me 🙁
Plz help
=INDEX(B2:H14,MATCH(A2,D2:D14,0),2)
The references need to be absolute. Otherwise named ranges will behave funny (as they are relative too).
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!
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.
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?
I have the same doubt. Could you please answer?
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.
Why vlookup doesn't work, but xlookup and indext +match works
This is because both XLOOKUP & INDEX (MATCH) return cell reference where as VLOOKUP returns the value.
What if i need to have image lookup for set of 10 images across atleast 100 records to show create badges earned bashboard
I am getting the error of reference isn't valid. What is the reason for that?
I have tried using both the functions, Xlookup as well as index-match.
Beware! Putting this in your workbook will cause macros to run almost 11 times slower.
For example, after implementing the linked picture object, a task took 12.12 seconds. Simply deleting the object and re-running the task it only takes 1.12 seconds.
This can be verified by rebuilding the object per the instructions and reruning the task. It once again takes forever and almost seems to crash Excel but eventually finishes.
Picture links are known to be slow. Have you tried Application.ScreenUpdating = FALSE at the start of the macro to see if that has any impact.
Hello.
I tried but this is not working for me. I have shared my file. Iv kept it on drive: (with edit permission).
https://docs.google.com/spreadsheets/d/1RkPSrMalKi9TrT4aiIa5kRTn5iY7fyD-/edit?usp=share_link&ouid=107220448150503936029&rtpof=true&sd=true
Kindly help.
I cann't press enter after typing =employee_picture. Please help explain. THanks