Lookup Picture in excel with Index Match

We know that the vlookup will retrieve the value from a table. But in case if we want to retrieve picture like vlookup, we have to follow below steps. Let us understand how to lookup picture in excel.

Vlookup can not do picture lookup, therefore we will have to use INDEX MATCH with some more tricks.

How to Lookup Picture in excel?

Following are the steps to lookup picture:

  • Prepare the data table with picture
  • Define Name with INDEX MATCH function 
  • Link Picture results through Named range
  • Create dropdown for selection (Optional).

Prepare the data table with picture

The details in data table with pictures are to be prepared in the sheet to create lookup picture. All picture in the table is to be adjusted to fit in one cell. 

Lookup picture
Lookup picture

Define Name with INDEX MATCH function

To search the lookup value in table, we can’t put formula directly in the cell. Alternatively, we have to define a name and add formula to that.

Lookup Picture in excel

We have to define a name with formula INDEX MATCH to search lookup value and retrieve picture for matching value as above

=INDEX('musical instruments'!$B$5:$B$11,MATCH('musical instruments'!$E$2,'musical instruments'!$A$5:$A$11,0),1)

Link Picture results through Named range

After Defining Name with formula, we have to copy a picture and paste that to the place where we want the result picture.

Select this picture and go to formula bar and enter the name defined with “=”sign.

Now when you change lookup value in E2 and the picture will change automatically.

Create dropdown for selection (Optional).

For selection of lookup value you can create a dropdown list with data validation. Read this article for more details:

How to insert drop down list in excel

You can download the file with example :

Leave a Comment

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