Excel's lookup functions recently welcomed the addition of XLOOKUP and XMATCH. However, neither these new functions or the existing ones don’t support image lookup. In this guide, we're going to show you how to make image lookup in Excel.
Preparing the images
First, we need to place the images into corresponding cells. Make sure that each image is completely inside their cells - there shouldn't be any overflow. Otherwise, you can only display the part of the image that fits inside of the cell area.
Setting up a user interface for image lookup
At this point, we are ready to build an interface for image lookup. In our example, we are going to update an image, based on a value from a dropdown selection.
To make the formulas and references easier to read we will using the names in the table, which were also used to generate named ranges.
- Select the cell containing the dropdown
- Activate the Data tab in the Ribbon
- Click on the Data Validation icon
- In the Data Validation dialog, set Allow to List
- Select the names in the table under Source
- Click OK
The dynamic image
This is the last and the most important step for doing image lookups in Excel. Although the lookup formulas do not support images, some of them can actually support this when the formula is used within an image.
Unfortunately, you can not simply click on an image and type in a formula. However, you can refer a named range, and that named range can contain a formula.
Start by opening the New Name dialog under Formulas > Defined Names.
Give a friendly name to the input, and move onto to the Refers to section to enter the lookup formula. You can use either XLOOKUP or INDEX-MATCH formulas. If your version of Excel doesn't have support for these formulas, use the INDEX-MATCH combination instead - VLOOKUP won't work with this method.
Choose one of the following based on your data:
Place an image where you want to display your dynamic content. The most important thing here is the size of the image: It needs to fit in the cell.
After placing the image, click on the image and activate the formula bar. Enter the name you created for the lookup formula.
Now, every time the Name dropdown is updated, the image will be updated as well. Excel simply returns an image of the cell into the image object.
