Excel Challenge 49: Create a Dynamic Lookup Tool With Images

Ready to put your Excel skills to the test? This challenge is all about building a dynamic lookup tool that retrieves player details — including images — based on a dropdown selection.

Excel doesn’t offer a built-in picture lookup function, but with the right formulas, you can work around this limitation and create a fully functional lookup tool. You’ll also design interactive PivotTables to organize and analyze player data with slicers and a birthday timeline.

To dive in:

1. Watch the challenge video for an overview of the scenario.
2. Review the instructions below to understand the tasks (and grab some hints!).
3. Download the Excel worksheet and start crafting your solution.

Share your approach with other Excel enthusiasts! Join our Slack community to exchange ideas, ask questions, and connect with fellow learners.

The challenge 🥷

The challenge dataset contains four worksheets:

• Challenge: Outlines all the questions you need to answer.
• Data: Holds the raw player information needed for lookups.
• Lookup: Features a dropdown in cell B1 to select a player. Below, you’ll display the player’s birthday, country, club, and picture.
• PivotTable: Where you’ll organize players by country, show total player counts, and add slicers for filtering by country, club, and federation. You'll also implement a birthday timeline for filtering by year.

Your tasks

1. Build the dynamic lookup tool

On the Lookup worksheet, there is a dropdown list containing player names. When a player is selected, retrieve and display the following details:

• Birthday
• Country
• Club

In addition to text-based details, set up a picture lookup to dynamically display the selected player’s image.

2. Create an interactive PivotTable with slicers

On the PivotTable worksheet, build a PivotTable that groups players by country and calculates the total number of players per country.

To enhance interactivity, add slicers that allow users to filter players by:

• Country
• Club
• Federation

Finally, incorporate a birthday timeline so users can filter players by year of birth and analyze trends over time.

The hints 🤩

You may find it helpful to revisit one of Excel’s most powerful combinations: INDEX and MATCH. A quick refresher on PivotTable slicers might also come in handy as you work through this challenge.

The solution ✅

Did you crack the code and complete all the tasks? Now it’s time to see how your solution stacks up!

Have a different method? We’d love to see it! Excel offers endless possibilities, and there’s always more than one way to solve a problem. Share your solution or suggest a new challenge in our Slack community, where you can connect with fellow Excel enthusiasts.

Enjoyed this challenge? Keep the momentum going by exploring our previous Excel challenges or diving into the Resource Hub, packed with tutorials, tips, and tricks to sharpen your skills.

Want more? Check out the GoSkills Excel course library to build expertise in the exact tools — like PivotTables — and techniques you need to tackle any challenge with confidence.