Excel 2019: Use Picture Lookup
June 06, 2019 - by Bill Jelen
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.
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.
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
=Icons$B$2 if you are on a different worksheet.
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.
Title Photo: Soragrit Wongsa at Unsplash.com