Picture Vlookup

Looking up pictures can be interesting and can possibly be used in different scenarios. Here is a quick sneak peak into how can you do a Picture Vlookup!!

 

To begin with we need the data !

Picture Vlookup2

We need pictures with names! (Download it here)

 

Creating a Setup

We a need a dropdown where the user can select the name of the employee. That can be created by data validation

Picture Vlookup3

 

  1. Select the cell in which you want the employee names drop down
  2. Go to Data Validation in the Data Tab (or use the shortcut ALT A V V)
  3. Select the list option and link the source to the names of the employees on the worksheet
  4. Done

 

Now a formula for looking up Pictures

What we want is the picture of the name selected in the drop down. Let’s write a simple Index Function here

Picture Vlookup4

 

=INDEX($C$3:$C$11,MATCH($F$3,$B$3:$B$11,0)). Lets deep dive into this formula

  1. INDEX([Range of Pictures] – We are looking up in the range where all pictures are kept
  2. =INDEX($C$3:$C$11,MATCH(Name Dropdown, List of names, Exact Match)) –
    • The second argument is to enter the row number, which is given by the MATCH function.
    • Please note that the MATCH function is matching the row number in the list of names and is resulting a number
    • That number is used in the Index to fetch that picture of the employee
  3. We will now use this formula to create a named formula range

 

Name the formula

We got to do a bit of cell naming here

Picture Vlookup5

 

  1. Copy the formula (by pressing the F2 Key)
  2. Go to the Name Manager in the Formulas Tab (use the shortcut Ctrl + F3)
  3. Create a New Name and Paste the formula in the reference

 

Get ready for some Magic

Picture Vlookup6

 

  1. Copy and paste any picture
  2. Link the Picture to the Named Formula
  3. Done.. Enjoy the Picture Vlookup

 

Download the Completed Picture Vlookup Excel File

 

Download The Ultimate VLOOKUP Guide

The Ulimate Vlookup Guide 1

The guide contains tricks and tips beyond the conventional VLOOKUP and it’s free – DOWNLOAD THE ULTIMATE VLOOKUP GUIDE (pdf)

 

More out of the Box Stuff

  1. Creating a Map Chart – Plot the cities on a Map of India
  2. Stock Ticker Chart 
  3. Convert a data dump into a visualization

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI