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 !
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
- Select the cell in which you want the employee names drop down
- Go to Data Validation in the Data Tab (or use the shortcut ALT A V V)
- Select the list option and link the source to the names of the employees on the worksheet
- 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
=INDEX($C$3:$C$11,MATCH($F$3,$B$3:$B$11,0)). Lets deep dive into this formula
- INDEX([Range of Pictures] – We are looking up in the range where all pictures are kept
- =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
- 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
- Copy the formula (by pressing the F2 Key)
- Go to the Name Manager in the Formulas Tab (use the shortcut Ctrl + F3)
- Create a New Name and Paste the formula in the reference
Get ready for some Magic
- Copy and paste any picture
- Link the Picture to the Named Formula
- Done.. Enjoy the Picture Vlookup
Download the Completed Picture Vlookup Excel File
Download The Ultimate VLOOKUP Guide
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
- Creating a Map Chart – Plot the cities on a Map of India
- Stock Ticker Chart
- Convert a data dump into a visualization
