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) 😛
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
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