Challenge 5

Highlighted
Occasional Contributor

Hi Every one

    I have problem in Vlookup for images in Excel, 
    Ho can I find Images by suing Vlookup in Excel Or a similar formula ? as i showed in attachments, 

Thank you for your cooperation in advance
 

6 Replies
Highlighted

@Jalal_1988 

 

Could you post your actual spreadsheet rather than an image. It's a lot easier that way for us to test a solution with the real thing.

 

Just glancing at your sheet, though, I see a potential problem...you have the images spanning rows (obviously done to differentiate "left" from "right" --

I've also personally never seen VLOOKUP work with images as the desired item, and wonder how the images are present in the sheet itself, whether that will make retrieval difficult.

 

 

Highlighted

@mathetes 
Dear in attachments you can find the Excel file

Highlighted

@Jalal_1988 

 

Two things:

  1. I don't think you can retrieve an image directly via VLOOKUP or any of the other compatable methods e.g., INDEX & MATCH). The reason,,,those images are really just pasted on top of the cell(s) in question.
  2. When you're designing a table such as this, it doesn't work to have some cells span two different rows. If you want the same data to be retrieved for two different part numbers (as here) just have the same entry in the cell in question.

 

A suggestion: if your third column contained a link to a file with the picture, it might be possible for the VLOOKKUP to retrieve that link and the link be used to retrieve the picture.

 

And I'd be happy if someone else here can make it work directly; I couldn't.

Highlighted
Maybe this will help you find a solution. It's a dynamic image where you could select an item from a dropdown menu and have the related image displayed in another cell.

https://www.youtube.com/watch?v=wlW2UKml9CY
Highlighted

@Jalal_1988

With reference to the video referred to by @JMB17, please find some additional comments below.

 

The key feature is that you have to create Named Ranges and indirectly reference them by a Named Formulae. Now, the video deals with one picture (a flag) that gets displayed based on a selection from a drop-down. You could do something similar for each of the items in your output range in column K based on the Item code in column J.

BUT!! Bear in mind that you need to get rid of the merged cells in column C. They only create trouble. Secondly, when you create named ranges from the Item codes in column B the hyphens "-" will be replaced by an underscore "_", as named ranges may not contain just any character. So, the code in column J will also have the contain the underscores, or the named formula that will display the picture in K2 for the item in J2, would have to look like this:

=INDIRECT(SUBSTITUTE(NJ!$J$2,"-","_"))

No VLOOKUP needed. It's all demonstrated in the attached file, although it needs quite some work to make it look nice.

Highlighted

@Jalal_1988 

The attached uses XLOOKUP to return a range with an image superposed.

INDEX/MATCH should work as well.

NameRefers to   
selected=XLOOKUP(shape,description,pictures)