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

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

img2
 

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

 
img3
 

  • Go to Formulas ribbon

 
img4
 

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

 
img5
 

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

 
img6
 

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

 
img7
 

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

 
 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube