Lookup an image using “Linked Picture” in #Excel
Perhaps you have used VLOOKUP to return a value from a table very often. However you cannot use VLOOKUP to return an image… So how the above can be done? You will need to know three tricks:
- Linked Picture
- INDEX & MATCH
- Named formula
You may download a sample file to follow along.
Let’s go through them one by one.
Linked Picture
1. Setup the lookup table
Note: Each image should reside in a cell wholly (I shall show you why later)
2. Create a Linked Picture
2.1 Copy C2 (not the image but the cell) –> Right-Click on F2 –> Paste Special (Linked Picture)
2.2 Select the picture just pasted and observe the formula bar (which is linked to C2).
Apparently this is why it’s called Linked Picture (aka Camera)
2.3 Try: Move around objects onto cell C2 and see…
See? The Linked Picture effectively shows whatever put on the linked cell (including border and color). That’s why it is important for us to put the image inside a cell wholly when we set up the look up table in step 1.
2.4 Making the Linked Picture responsive to input
The below shows that we can do it manually by changing the reference in formula bar…
By understanding this, what’s next is to write a formula that gives dynamic result according to input on, say E2. INDEX and MATCH are the ideal functions in this situation.
The formula we need:
=INDEX($C$2:$C$5,MATCH($E$2,$B$2:$B$5,0))
Tip: INDEX, on top of, returns a value could also returns a reference.
2.5 Can’t wait to replace the above formula with the “=$C$2” …
It did not work on Linked Picture… so frustrating.
No worry! Named formula comes to rescue.
Named formula
1. Go to Formulas tab –> Define Name
1.1 Input a name, and copy and paste the formula to “Refer to:”
Final step – Assign the Name to the Linked Picture
-
Select the Linked Picture –> type “=Pic” in the formula bar
Now the Linked Picture refers to a dynamic reference that is responsive to the input in E2.
As simple as this. 🙂
Together with a few other tricks, we may create something interesting… like an Interactive CV posted on About.
Don’t you think this kind of CV will make you stand out from the crowd?
(Well… it really depends) 😛
Note:
If you put your linked picture on another worksheet, you have to pay extra attention on the formula you put in Name Manager. You have to include the sheet name in this case:
=INDEX(Content!$C$2:$C$5,MATCH(Content!$E$2,Content!$B$2:$B$5,0))
If your sheet name contains space, you need to wrap the sheet name with ‘, e.g.
=INDEX('My Data'!$C$2:$C$5,MATCH('My Data'!$E$2,'My Data'!$B$2:$B$5,0))
I’ve followed your guide completely and even copied the table layout and what cells were referenced. However, it still does not work? You dont explain when/how to add in the data validation list so feel this may be where im going wrong as everyhting else is identical
LikeLike
Hi Adam, the data validation list is not the key to lookup a picture. Pls check carefully the formula you put in the Name manager… a single typo or missing of $ may lead to errors.
LikeLike
Nice but how can we make dynamic to look up pictures as multiple cells instead of one cell.
LikeLike
Hello,
It works!…all the others I tried left me with the dreaded “Reference isn’t valid” error.
Nice job!
LikeLike
Thanks. Glad it helped! 😀
LikeLike
I got it to work but what if i had a workbook where i did not yet have all my pictures but I wanted it to look up the ones i did have and then look up the new pictures as they were added without having to do a new formula.
I have my workbook laid out in a way that it operates the lookup off of a dynamic lists so for some selections it has all the photos to look up and for other selections it doesn’t have but some photos. I get a reference not valid when it is trying to look up photos that are not available for the selections but i need that formula to work for when the selections changes to one that has all photos.
Is there like an iferror or something like that you can use that will return a nothing if there is nothing to return rather than the Reference Not Valid message?
LikeLike
first thing very good explanation here very easy to understand –
still if you have any doubt or you want to know why it shows “Reference is not valid” for image lookup try this link – https://youtu.be/S1Kyw7iirEI
LikeLike
Pingback: Nintendo Console Dashboard Update – Static Interest
hi.. can’t we use it in two tabs or two worksheets. detail table in a one tab & drop list in a another tab. i tried it in several time & every time get “reference is not valid” message.
LikeLike
Hi Vishwa, it should work.
Pls check carefully the formula you put in the name manager for the linked picture. A single typo, or miss of a single quote would return the error.
Hope It helps
LikeLike
It didn’t work for me. I got a “Reference Isn’t Valid” message. I tried following similar steps on another web page and got the same result.
LikeLike
Please check the formula you put in the “Refers to” in Name Manager. It’s very likely that there is something wrong there. Hope it helps.
LikeLike
whoah this blog is excellent i like studying your articles. Stay up the great paintings! You realize, a lot of individuals are hunting around for this information, you can help them greatly.
LikeLike
Thank you very much for your kind words, Cindy. ☺️
LikeLike
I’m now not positive the place you’re getting your info, but good topic. I needs to spend a while studying more or figuring out more. Thanks for excellent information I was searching for this information for my mission.
LikeLike