• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / Create a Picture Lookup in Excel

Create a Picture Lookup in Excel

Many Excel users are familiar with lookup functions such as VLOOKUP, INDEX and MATCH to look up information in a list. But how about performing a picture lookup in Excel to return a picture dependent upon the contents of a cell.

This requires a little extra thought as a standard VLOOKUP is not capable of returning a picture from a list.

In this blog post, we will explore how to create a picture lookup. We will look at how to return the picture of a flag dependent upon the country name that is selected from a list.

Spreadsheet Setup

In this example we have a list of countries and their flag. It is very important in this list that the picture (flag), is completely inside the cell. You can see the white space between the frame of the picture, and the borders of the cell containing it.

Cell D2 contains a drop down list with each countries name. When a country is chosen we wish for the corresponding flag to be returned.

Although a drop down list is used in this example, the data used for your lookup can be the result of any formula, or data entry method.

Country flags to return with a picture lookup in Excel

Create a Picture Lookup in Excel

As it is not possible to write a lookup function in a cell to return the picture, we will enter the formula into a defined name. The INDEX and MATCH functions have been used to perform the lookup.

  1. Click the Formulas tab on the Ribbon and then the Define Name button.
  2. Enter a name for the defined name such as FlagPic
  3. Click in the Refers to: field and enter the following formula.
=INDEX(Sheet1!$B$2:$B$5,MATCH(Sheet1!$D$2,Sheet1!$A$2:$A$5,0))
INDEX and MATCH formula to lookup pictures in Excel

Linking the Picture to the Formula

Now we need to link the picture in cell E2 to the defined name.

  1. Select the picture.
  2. Enter =FlagPic (or whatever name you used) in the Formula Bar and press Enter.
Link picture to a formula using a defined name

And that is it. When a different country is chosen from the list in cell D2, the appropriate flag is returned.

Watch the Video

Advanced Excel tricks online course

Related Posts:

  • VLOOKUP with Conditional Lookup Table
    VLOOKUP with Conditional Lookup Table
  • What You Need to Know About Dynamic Array Formulas in Excel
    What You Need to Know About Dynamic Array Formulas in Excel
  • Sort by Drop Down List in Excel
    Sort by Drop Down List in Excel
  • Excel FILTER Function – The Best Function in Excel
    Excel FILTER Function – The Best Function in Excel

Reader Interactions

Comments

  1. Shine Matias says

    28 March 2017 at 5:27 am

    Works perfectly!

    Reply
  2. KingTamo says

    3 December 2017 at 4:43 pm

    That’s awesome
    Thanks a lot for this great tutorial
    Can you please attach a file of those tutorials to keep those treasures?

    Reply
  3. Daniel says

    31 January 2018 at 5:24 pm

    Superp…

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·