How to Insert a Picture in Excel Using a Formula

Example 1 – Combining INDEX and MATCH Functions

Steps

Insert images using the Insert tab.
In Insert, go to Illustration.
Click Pictures > This device.

Choose an image file and click OK.
Repeat this process to add more images.

Add a dropdown menu to the sheet.
Open a new sheet to add the dropdown list.
Go to Data > Data Tools.
Click Data Validation.

In the dialog box, select List in Allow.
Select B5:B7 in Source .
Click OK.

A dropdown menu is displayed in the new sheet.

You can switch the image names and change the image name entry in C4.

Define the Name.
In Formulas, click Define Name.
Click Define Name.

In Edit Name, set the Name to Logo.
In Refers to, enter the following formula.

=INDEX('Image Sources'!$C$5:$C$7,MATCH('Logo Selection'!$C$4,'Image Sources'!$B$5:$B$7,0))

Click OK.

Formula Breakdown

MATCH(‘Logo Selection’!$C$4,’Image Sources’!$B$5:$B$7,0): This function will look for the logo name in the dropdown menu in the Image Sources sheet. It returns the position of the value matched in the Image Sources sheet.
INDEX(‘Image Sources’!$C$5:$C$7,MATCH(‘Logo Selection’!$C$4,’Image Sources’!$B$5:$B$7,0)): After the MATCH function returns the position of the matched data in the Image Sources sheet, the INDEX function returns the value according to the position in C5:C7.

Select C5 in Image Source and press Ctrl+C.

Go back to the Logo Selection sheet.
Select C5 and in the Home tab, click Paste.
Choose Linked Picture.
The copied images are pasted into C5.

The first image from the image source sheet is in the Logo Selection sheet. You can choose any image and copy and paste it to the Logo Selection sheet.
You can resize the image.

Select the image and enter:

=Logo

This logo is the one that was created and used in the Edit Name dialog box.

In the dropdown menu, images change.

Example 2 – Creating a Dashboard Using Images

Gather information about the person’s name, age, hometown, and country.

Add the images you want to compile with the information in a separate sheet.

Add information and a dropdown menu to the sheet.
Open a new sheet to add the dropdown list.
To add a dropdown list, go to Data > Data Tools.
Click Data Validation.

In the dialog box, select List in Allow.
Select B5:B7 in the image sheet as the data range for the Source dropdown.
Click OK.

There is a dropdown menu in the created sheet.

Switch between the Person Name and change the Image Name entry in C4.
Add formulas to D7, D8, and D9.
Select D6 and enter the following formula:

=VLOOKUP(C4,'Data-Source'!B5:E7,2,0)

Select D7 and enter the following formula:

=VLOOKUP(C4,'Data-Source'!B5:E7,3,0)

Select D8 and enter the following formula:

=VLOOKUP(C4,'Data-Source'!B5:E7,4,0)

Define the name.
In Formulas, click Define Name.

In Edit Name, set the Name to Pictures.
In Refers to, enter the following formula:

=INDEX(Image!$C:$C,MATCH(Dashboard!$C$4,Image!$B:$B,0))

Click OK.

Formula Breakdown

MATCH(‘Logo Selection’!$C$4,’Image Sources’!$B$5:$B$7,0): This function will look for the Person Name in the dropdown menu in the Dashboard sheet. It returns the position of the value matched in the Image sheet.
INDEX(‘Image Sources’!$C$5:$C$7,MATCH(‘Logo Selection’!$C$4,’Image Sources’!$B$5:$B$7,0)): After the MATCH function returns the position of the matched data in the Image sheet, the INDEX function returns the value according to the position in C5:C7.

Go back to the sheet containing the image files.
Select C5 and then press Ctrl+C to copy the first image.

Go back to the Dashboard sheet.
Select C5 and in the Home tab, click Paste.
Choose Linked Picture.
This will paste the copied image into C5.

The first image in the Image sheet is copied to the Dashboard sheet. Choose any image and copy and paste it.
You can resize the image.
Select the image and enter:

=Pictures

This Picture was created in the Edit Name dialog box.

Change the Person Name using the drop-down, the Image, Age, Hometown, and Country will change.

How to Insert a Picture Using a VBA Code in Excel

Steps

Add images to the sheet automatically using the VBA code.
The code sets that: every time Yes is entered into a cell, the image will be uploaded into the specified cell from the specified folder.

Right-click the sheet name and click View Code.

In the module window, enter the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Address <> "$E$5" Then Exit Sub
If Target.Value = "Yes" Then
Application.ScreenUpdating = False
Range("C6").Select
ActiveSheet.Pictures.Insert( _
"E:\Onedrive - Softeko\OneDrive\Softeko\5025_62-0071_Rubayed Razib_how to insert picture in excel using formula\Exceldemy-logo-small.jpg").Select
Selection.Name = "PictureName"
Range("A2").Select
Application.ScreenUpdating = True
Else
On Error Resume Next
ActiveSheet.Shapes("PictureName").Delete
End If
End Sub