Excel VLOOKUPs With Pictures!

Another SQA and one that a lot of people ask about!!!

We know that VLOOKUP's are really easy for pulling words and numbers from a table...everyone can do that, but can you use a VLOOKUP to pull an image? Well, yes you can...we just need a bit of set up first...

First up...paste in your pictures against the relevant name, and make sure you resize your CELLS to fit your pictures (or the other way around)...either way, the picture must NOT overlap any of its cell borders...it's also very useful if all the pictures have similar dimensions (identical would be even better)...

Next we add a label to the CELL that each picture lives in. We can do it manually but it's quicker to use the name manager and chose create from selection...

Then we will copy the CELL (not the picture) that contains one of our pictures...any one, it doesn't matter as we'll correct it later, and PASTE SPECIAL...LINKED PICTURE where we want the looked up picture to appear.

We now have picture OF THE CELL with a picture in it (note the distinction there)...if you click the picture (of the cell), you will see the address for the original cell in the formula bar...with me so far?

Next we add a new named range for our picture...in the "refers" to we will say INDIRECT(click the cell containing their name...which is the SAME NAME AS THE LABEL FOR THEIR PICTURE).

Finally, we overwrite the cell reference in the formula bar for the picture, and replace it whatever the name of that last named range we just created...watch it through, it's magic!

This is a sample lecture from my course, Microsoft Excel Essentials: Level 2 - Intermediate/Advanced.

Get this course now!

Level 1: Microsoft Excel Essentials: Level 1 Basics - Excel Made Easy
The complete 4 hour course is yours for a low cost monthly subscription (first 2 months free!):

Level 2: Microsoft Excel Essentials: Level 2 - Intermediate/Advanced
The complete 9 hour course is yours for a low cost monthly subscription (first 2 months free!):

Level 3: Microsoft Excel Essentials: Level 3 - VBA Programming + Macros

Alternatively, you can purchase the complete set in...
Excel Essentials: The Complete Excel Series - Level 1, 2 & 3

The WHOLE 27.5 Hour Excel Mastery Series In One! From Excel Novice To VBA Programmer...
Available to buy with lifetime access for just $16.99 (normally $200) here:

This course is for suitable Excel 2007, Excel 2010, Excel 2013, 2016, 2019 & 365

Videos in the Level 1 series:

Videos in the Level 2 series:

Videos in the Level 3 series:

[alvenbalatan]

this is the most simplified i've ever seen so far... thankssss a lot Alan, you're so amazing.... more power to you

[jolodavid]

Hello guys, for those having a "reference isn't Valid" error, try removing(or replace by underscore) the space and special characters in your pictures name, excel only allow letters, numbers and underscore they automatically replace space and special character by underscore.

[fakhruddinkagzi]

thankyou for this video where you have explained very well how to return a pic in a cell by using name manager. issue is that it is technically working, but not instead of returning the pic, it is showing grey borders..something changes, but it's not the picture

[jennifermioni]

Hi Alan! How cool is this feature!!! My question though, I have my "mugshot" cell referencing a =vlookup cell and when the data changes in the vlookup cell the mugshot cell doesn't automatically change pictures until I click on the picture. Is there a way to make it automatically change when the data in the vlookup cell changes? (vlookup cell references a state based on how many miles driven - if they type in 700 miles, Kansas will pop up, if they type in 2400 miles, California will pop up etc)

[nikhilbharadwaj]

Hi Alan, Thanks a lot for the video.The explanation was crystal clear. I tried using this technique for my application, I keep getting an error "Reference isn't valid" when I try to re-name the photo cell with the Defined name. Could you please help? Thanks.

[Rodda]

Hi Alan. Your video was really helpful but I have a question. I set mine up with over 1000 photos of students and the last 30 photos on my list won’t change. I assume my data list is short but I don’t know how to fix it! Any advice?

[stanchomarinov]

Hi Alan, thank you for the tips i found your video very helpful, however as I'm completely new to the excel and everything i know is pretty much from videos like yours. My problem is as for most of the tasks i am using query formulas, but now i just stuck where i have a column which i put into the cell picture of barcodes . For example here is a simple formula which i am using : =QUERY('MASTER FILE'!A2:J, "SELECT A, B,J,D WHERE D ="""&D3&""" ") .
So where is the column 'J' there are many barcodes which i would like somehow to populate with the other columns . The formula is working but no matter where i choose to put J, simply it does not show anything on that specific column. Is there any chance to modify or actually how to populate certain columns depending on the drop down menu as it is in the formula WHERE D column = D3 . For example i got a recruiting big master file end collecting all data . My idea is all pictures personal document photos to put them into the cell for future audit if need. I just want simply to populate what i got as a picture in any column to come along with the others. Thank you so much

[phillipknott]

(still learning) I need help duplicating the information in another row. When I do, it changes all the images at same time.

[joebrazeau]

Hello. great tutorial. everything worked right up to the end where I typed the name in that selected cell but the image disappeared.

[lucyeddy]

Is there a way to automate this for a whole column of cells? I have a list of pupils, their percentile rank, and I want to insert a picture relating to their rank in the column next to it. I have 300+ pupils in the list, and they are ranked on 7 different tasks so doing this for each one would take ages!

[jsxpzff]

Hi Alan - Great video 👍👍 Would be possible to "built" upon the picture with further "pictures" into an all ready chosen picture ?

[BenLoom]

for people having ref errors, it appears it doesn't work with spaces in words

[acjintrodesigns]

Hi Alan, I'm having a hard time even watched 4 videos on the same thing done differently, i still can not manage to get what i am doing right. i am trying to use a product code to pull up a barcode but it just does not want to work.

[sherlockkhan]

Hi Allan...really considering doing your course !...but I have a question ....My goal is to create an excel spreadsheet that will work as a recipe system for me to track my ingredient list and the costings to see in the end how it affects my final product cost plus be able to change different ingredient costs as they may occur and it automaticly adjust my end cost price.Will your course cover these kinds of applications of excel?

[SuperWolfkin]

Okay while brilliant where did you use vlookup.

Still I can use this. This might be the easiest solution ive found

[EVLitterPicker]

Hi Alan, thanks for this :)

Do you know if there is a way to display an image in each row of a table based on a criteria?
Say for example a table with a bunch of values, but in row B2 is the name of the person and in A2 have the picture of them? So that each time I add a new row, their picture appears when I type their name.
In other words, a method of not using a pulldown and having multiple images instead of just 1 displaying at any given time?

In my worksheet, I require around 20 images which need to remain on display at the same time independently, based on their respective criteria in column B.
The list is currently 300 rows long, fortunately the images sizes are negligible :)

[sonimathew]

How to put v lookup from folder to excel

[jobethflores]

Hi Alan, why is it the image didn't appear in me using the MugShot?

[TheTwinkieFairy]

Has anyone had issues with the pictures/picture of a cell being cropped strangely or shifting in the place you are using the indirect reference? For example, when you search Ashley here, maybe it only shows half her face and she's right justified instead of centered?

[sharoonswing]

Hi, Alan, what would be the v look up for displaying a picture after a badge is scanned?