MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel 2020: Use Picture Lookup

June 10, 2020 - by

Excel Use Picture Lookup. Photo Credit: Soragrit Wongsa at Unsplash.com

I love this technique, which essentially does a VLOOKUP that returns a picture selected in response to a formula answer. In the image below, make sure that your lookup table starts in row 2 or below. It cannot start in row 1.

In cell C6, someone enters how many passengers need to be accommodated.

A cool new function called IFS in C7 figures out which row in the table contains the picture that you want to show. In this case, there are 7 passengers, which means you need a 12-passenger van.

Those icons are an Office 365 feature found under Insert, Icons. But you could use any clipart or photos.

The table below appears on a worksheet called Icons. The OFFSET function in A10 tells Excel to start in Icons!$B$1, move down 3 rows, move over 0 columns, and select a range 1 row tall by 1 column wide.

A2:B4 contains a picture lookup table. Column A has Capacity values of 1, 4, and 12. Column B has pictures of a motorcycle, car, and bus. In Row 6, you enter a number of people you have to transport. C7 calculates which row has the right picture with =IFS(C6=1,1,C6<=4,2,C6<=12,3). You can use =OFFSET(Icons!$B$1,Icons!$C$7,0,1,1). In the next figure, that formula is assigned to a name of WhichPicture.

I always build the OFFSET function in a cell to ensure that I don‘t get any syntax errors. But when you use the technique described here, you cannot use OFFSET in a cell. You have to copy the formula and use Formulas, Define Name and create a name that refers to the OFFSET formula.

In the Define Name dialog, type a name of WhichPicture. The Refers to is =OFFSET(Icons!$B$1,Icons!$C$7,0,1,1).

Copy the cell containing the first picture in your table. Go to the dashboard and use Paste Picture Link, as discussed in the previous topic. Look in the formula bar, and you see that this linked picture is coming from =$B$2, or =Icons$B$2 if you are on a different worksheet.

Copy the Motorcycle in B2 and Paste Picture Link. If you have the linked picture selected, the formula bar shows =$B$2.

With the linked picture still selected, click in the formula bar. Change the formula for the linked picture to point to the name that you gave to the OFFSET formula. Amazingly, the picture will now update any time that the calculation in C7 points to a new vehicle.

Change the formula in the formula bar from =$B$2 to =WhichPicture. The picture changes from a motorcycle to a bus.

Mynda Treacy taught me the picture lookup in her amazing dashboard course.

Title Photo: Soragrit Wongsa at Unsplash.com

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.