Formula to insert a picture based on various cell values, w/out VBA, Macros, etc.

[FlynHokie]

Likely a tough one here.

Simplifying a complex worksheet here. But basically:

A1 = Value
A2 = Value
A3 = Value
A4 = Value
A5 = Picture based on A1:A4 values.

Limitations: Pictures have to be referenced from within the excel Workbook. Not external to a referenced folder or website. I can not use VBA, Macros, or any other non-basic Excel functions due to insane limitations put on my machine that can not be bypassed.

Was hoping to use VLOOKUP or similar , with a table build referenceing every combination of values that would work for each picture, but the VLOOKUP referenced cell with inserted picture just returns a 0 value, not the image.

I can build the various 500+ combinations of worksheets that will be created by varying values of the critera cells to pull one of a 50+ images, however, I'd rather not have to do that, and would like to keep it automated.

 

[Domenic]

In this example, I've kept things simple. But you should be able to adopt it for your purposes.

1) First, let's assume that we have the following set-up, where A2:B4 contains our lookup table. Also, cell D2 is where we'll specify the country of interst, and E2 is where the corresponding picture will be returned.

2) Select cell B2, not the picture, but the cell itself. Then click on Copy on the Ribbon under the Home tab. Then select cell E2, then click on the Paste drop down arrow on the Ribbon under the Home tab, and then select Linked Picture.

3) Then define the following name (Ribbon >> Formulas tab >> Defined Names group >>> Define Name)...

Name:  MyPicture

Refers to:  =INDEX('Sheet1'!$B$2:$B$4,MATCH('Sheet1'!$D$2,'Sheet1'!$A$2:$A$4,0))

Click OK

4) Select the picture in cell E2, and then replace =$B$2 in the formula bar with =MyPicture, and press the ENTER key...

Now, change the lookup value in D2, and the corresponding picture should be returned in E2.

Hope this helps!

 

[MrRajKumar]

Here is one way if you are using 365.

1: Insert your pictures for the values as "Place in Cell" (Insert > Pictures - Place in Cell)

Let's say, your pictures are in B1:B4 & corresponding values are in A1:A4.

In C1, enter your value from A1:A4, then simply use XLOOKUP in D1.

=XLOOKUP(C1,A1:A4,B1:B4,"Not Found")

 

[FlynHokie]

Thanks All. Raj - PLace in cell isn't available in my version of Excel - even though it's 365.

Domenic - Your solution works! Thanks much. A few issues I'm workign through now - the image as are 2.7"h x 3.58"w. So cell sizes are that height / width.

The INDEX/MATCH works great! Thanks so much!

Key note - images have to be identical in size, otherwise you start to get partial or combined images pulledu p in the call-out cell.

 

[Domenic]

That's great, glad I could help.

Cheers!