Featured Product

Excel Daily News

Excel 2019: Use Picture Lookup

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 =$B$2, or =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.

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.

I have a procedure that moves some buttons. They need to be moved to just below the bottom of the last entry in column A.
I get an error saying inval

Let's Connect

MrExcel.com debuted on November 21, 1998.

MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on this
web site are provided "as is" and we do not guarantee that they can be used in all
situations.
This site contains affiliate links. Any affiliate commissions that we
earn when you click a link to Amazon or other sites is reinvested in keeping MrExcel.com
running. You can earn a commission for sales leads that you send to us by joining our
affiliate program.
View our Privacy Policy, Cookies Policy, and Terms of Use.