Extract Image using Index and Match

 

In this article, we will learn how to retrieve picture, using Index & Match functions.

We have following name of the pictures in column A & the respective picture in column B.

 
img1

 

  • The objective of this article; as the user enters the name of any picture from Column A in cell D2; the related picture should be displayed in cell E2.
  • We will use a combination of Index & Match to get the output
  • The formula would be used in Name Manager to populate the result in cell E2
  • Click on Formulas tab & select Name Manager or Press CTRL + F3;
  • Enter the formula as =INDEX(Sheet1!$B$2:$B$5,MATCH(Sheet1!$D$2,Sheet1!$A$2:$A$5,0))

Note: All the ranges are enclosed with absolute reference to avoid any error while using Define Name in Excel as the formula applied in name manager will keep on changing whenever cell is changed; if used as relative reference or mixed reference.

 
img2

 

  • The next step is to insert any picture & or you can simply copy any one of the images from column B to avoid resizing of the image.
  • Click on newly inserted image; Go to Formula Bar & type “=Lookup”. Refer below snapshot:

 
img3

 

 
img4

 

As we press Enter, the formulas will take over & show the related picture. Similarly, when we change the name of the picture in cell D2, then the result would also get updated.

In this way, we can retrieve a picture using Index & Match functions.

image 19

Download – Extract Image using Index and Match – xlsx



One thought on “Extract Image using Index and Match

  1. I can’t get this to work. Your help would be greatly appreciated.

    I’m stuck at this step:
    Click on newly inserted image; Go to Formula Bar & type “=Lookup”.

    I’m unable to enter any formulas when the image is currently selected.

    I downloaded your Excel file & even tried to create a new Lookup Picture & Result boxes, but I’m unable to enter “=Lookup2″ for the new Result picture.

    Any hints?

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>