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:
You may download a sample file to follow along.
Let’s go through them one by one.
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.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:
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.
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) 😛
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:
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))