Using Named Ranges for Picture Lookup in Microsoft Excel 2010

If you want a formula that will return picture based on the lookup value, you can use a combination of INDEX& MATCH functions in Named ranges to get the output.

INDEX: Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

Syntax: =INDEX(array,row_num,column_num)

MATCH function searches for a specified item in a selected range of cells, and then returns the relative position of that item in the range.

Syntax =MATCH(lookup_value,lookup_array,match_type)

Let us take an example:

We have animals’ name in column A & their pictures in corresponding cell in column B.We want a formula to match the animal name in cell D2 & based on the criteria we will extract the pictures of the animal.

Click on the cell B2 & make sure you have not clicked on the picture.
Copy the cell & right click on it.
Select Paste Special & click on “Linked Picture” as shown in below snapshot.

After selecting the Linked Picture option, you will see the picture of tiger is linked in cell E2.

Go to Formulas ribbon

Click on Define Name. Then, the following dialog box will appear.

Enter name of the range in Name box.

In Refers to box, enter the formula as =INDEX($B$2:$B$4,MATCH($D$2,$A$2:$A$4,0))

Click on OK
Select cell E2 & go to formula bar
Enter equals to (=) “MyAnimal” the name that you have entered at the time of defining names & press Enter.

Now you can change the name in cell D2 to Leopard or Lion to check the results in cell E2 in the form of images.

Steve
August 12, 2018 at 8:44 pm

Can't make index function pass the image baes on drop list selection. The numerical contents of the cell pass through properly, but not the image(was formatted to move and size with cell). What am I doing wrong. Excel 2011 Mac.

Reply ↓