How to lookup an image in #Excel

Lookup an image using “Linked Picture” in #Excel

Excel Tips - Lookup image

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:

  1. Linked Picture
  2. INDEX & MATCH
  3. 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)Excel Tips - lookup image 1

2. Create a Linked Picture

2.1 Copy C2 (not the image but the cell) –> Right-Click on F2 –> Paste Special (Linked Picture)Excel Tips - Lookup image1

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)

Excel Tips - lookup image 2

2.3 Try: Move around objects onto cell C2 and see…

Excel Tips - Lookup image2

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…

Excel Tips - Lookup image3

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” …

Excel Tips - lookup image 4

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

Excel Tips - lookup image 5

1.1 Input a name, and copy and paste the formula to “Refer to:”

Excel Tips - lookup image 6

Final step – Assign the Name to the Linked Picture

  1. Select the Linked Picture –> type “=Pic” in the formula bar

Excel Tips - lookup image 7

Now the Linked Picture refers to a dynamic reference that is responsive to the input in E2.

Excel Tips - Lookup image

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

Update Your Excel Skills

Advertisements
This entry was posted in Excel Tips, Formula and tagged , , . Bookmark the permalink.

8 Responses to How to lookup an image in #Excel

  1. Pingback: Nintendo Console Dashboard Update – Static Interest

  2. Vishwa says:

    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.

    Like

    • MF says:

      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

      Like

  3. chesapeakejet says:

    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.

    Like

    • MF says:

      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.

      Like

  4. Cindy Cobb says:

    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.

    Like

  5. Alex says:

    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.

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s