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:
30 Responses to “Great News: Chandoo becomes MVP”
Congratulations! It's well deserved. 🙂
This is amazing. Hearty congratulations and a rocking new year ahead!!!
Congrats! I have learnt alot from your site... and the most important is learning how to achieve the most with the simplest concepts.
Thanks for the excel calendar. Is it possible to get a simple big fonted calendar printable on an A4 size paper without any distracive notes or visuals? BTW, I have already signed up for your newsletter. With warm regards and
Gratefully yours
50+ year old CHarish.
Hey Chandoo,
Great to hear that. Congratulations! The best new year gift, I would say. Keep it up, u've been doing extraordinary work for the excel users community.
Regards,
Pankaj Verma
Congrats dude... fantastic news!
congratulations! your site is great, this is well deserved
Rich
I recently found your site, I visit many. The tips that you provide are in the top 1% of all the sites I visit. Keep on Excelling.
Arnold
South Africa
Congratulations, Chandoo! That's a great way to start the year and make the PHD even better.
Congratulations, Chandoo.
Your site is one of most useful on the net. Happy new year and lot of ideas you will present for us.
Congrats.
Just read your name in an email from Abhishek. Well deserved.
Congratulations, and Happy New Year.
Greetings from Rio de Janeiro my friend! You trully deserve it!
Nive way to start 2009! Keep up the good work!
FC
That was quite forseeable , so you have now really got your PhD in excel.
Anyways Chandoo you have made excel a real Fun doo
I will like you to write some more on INDEX and MATCH function in near future.
@Hey Chandoo ! Congrates....
Ab to treat mangta hai !
Well deserved Chandoo!!
Congrat's!! Very well deserved 🙂
i always browsed mr.excel and used to see MVP writtne below names of people who used to solve queries in excel forum there......i just used to admire as to what they have special in them that they are MVP......
but now i got my answer...............u deserve it man..........
@All: thanks everyone 🙂
Congratulations Chandoo, nice job!
Chandoo,
A well deserved recognition and a good start to the New Year. Continue your good work.
Subbu
Many Congratulations.
You deserve a Ph.D. 🙂
congrats.....
Congrats dude. Rock on!
[...] charting community in 2007 and has been growing strongly ever since. In year 2009, I have received the MVP award from Microsoft. Just few days back I have become a dad [...]
Respected sir,
I am impressed!.... Good job done.. Keep it up...
Sir, How to be a MVP certified person. What level of knowledge is required for it? send me links if possible.
Please reply...
Regards,
Dipak Khalasi.
Dipak -
The first thing you need to cultivate is the ability to search the web effectively. You could start by Googling "Microsoft MVP".
[...] boy and girl which has been made hectic and incredibly fun ever since to their life.He has been awarded MVP status in 2009 by Microsoft(and renewed in 2010,2011 & 2012).His MVP profile is here.If you want to contact him direct then [...]
Congrats Chandoo!!
[…] Chandoo becomes MVP […]