## 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:

2. INDEX & MATCH
3. Named formula

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:

`=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.

## Final step – Assign the Name to the Linked Picture

1. ### 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))`

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips, Formula and tagged , , . Bookmark the permalink.

### 20 Responses to How to lookup an image in #Excel

1. Princess says:

hi i just used this look up image and done it successfully but after i linked the images upon search it became blurry? what should i do then?

Like

2. Vintage says:

Hi MF,

Thank you for such a detailed guide. I have a question.
I use vlookup for looking up images which are approximately same size as A4 sheet standard.
These images are containing technical drawing of equipment with quite small fonts. Even though the original high-res source image placed in two merged cells (409 height each), in the refereed pictures it becomes blurred.

Is there a way to increase quality of linked cells content ?

Like

• MF says:

Hi Vintage,
You are welcome.
I didn’t know there is a setting to control the quality of a linked picture. As a General rule based on my experience, if the size of the linked picture is the same as the original item, the display quality is the best.

Like

3. MARCMAC says:

Hi, Thanks for your great work. In my case I have a Macbook Mac OS Catalina with Excel for Mac 19. When I copy the cell with the image and I past the linked image I have the pasted image displaced in the cell and resized (zoomed in ). I can not figure why. I tried with many format of photos in the original cell. Any idea why ? Many thanks.

Like

• MF says:

You are welcome. I don’t have the version that you are using… not sure what and why is it. Sorry. Suggest you post your question to Excel forums. This is my favorite one:
https://www.mrexcel.com/board/forums

Like

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

Liked by 1 person

• MF says:

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.

Like

5. M says:

Nice but how can we make dynamic to look up pictures as multiple cells instead of one cell.

Like

6. Christopher J says:

Hello,
It works!…all the others I tried left me with the dreaded “Reference isn’t valid” error.
Nice job!

Like

• MF says:

Thanks. Glad it helped! 😀

Like

• CGardenhire says:

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?

Like

• EazyExcel says:

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

Like

7. 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

8. 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

9. 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

• MF says:

Thank you very much for your kind words, Cindy. ☺️

Like

10. 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.