Microsoft Excel

2 minute read

Excel Challenge 7: Create an Expandable Dropdown List

Claudia Buckley

Claudia Buckley

Join the Excel conversation on Slack

Ask a question or join the conversation regarding Excel challenges on our Slack channel.

Download your challenge data

Start working on the Excel challenge with this data workbook


It’s time for another Excel challenge!

This time, we’ll need to put our lookup skills to the test - specifically XLOOKUP. Here’s what to do:

  • First, watch the challenge video and read the instructions below the video.
  • Review the previously published video(s) and article(s) suggested.
  • Download the Excel worksheet you will use to complete the challenge tasks.
  • Put yourself to the test!

Want to chat about your approach and process with other Excel-heads? Join our Slack channel to share your insights and questions with like-minded learners.

The challenge



Here’s the scenario to be solved from the download file:

Download your challenge data

Start working on the Excel challenge with this data workbook

The bookstore on the corner has their book database entered in an Excel workbook table. The store owner wants to be able to query a Book ID and extract that entire row from the database.

Excel challenge 7She also wants to minimize input errors by having employees select the Book ID from a dropdown list in cell A2. She knows how to create dropdown lists, but is having trouble setting up one which expands or contracts dynamically as books are added and removed from the database. 

Once you’ve helped her with that, write a formula in row 5 that uses the selected Book ID to pull the correct row from the Database.

There’s more than one way to approach this, but what’s the most efficient XLOOKUP solution to this problem? If you don’t have Microsoft 365, no worries. Use Office Online and you’ll have access to the XLOOKUP function. 

Need a refresher? Check out these videos, then you’re ready to tackle this task.

And for you Excel whizzes out there - tell us what alternative worksheet functions can be used to complete this challenge.

Have fun!

The solution



We hope you'll enjoy taking part in this challenge!

Stay tuned to the GoSkills Excel Resource hub for more Excel challenges, and check out our range of expert-led Excel courses for all skill levels to further sharpen your skills.

If you enjoyed this challenge, try the Basic and Advanced Excel course to help you learn more essential formulas, functions, and practical real-world Excel skills.

Ready to become a certified Excel ninja?

Start learning for free with GoSkills courses

Start free trial

Join the Excel conversation on Slack

Ask a question or join the conversation regarding Excel challenges on our Slack channel.

Claudia Buckley

Claudia Buckley

Claudia is a certified Microsoft Office Expert (Excel Specialist), project manager, and business skills instructor at GoSkills who has spent over 20 years in employee professional development. Claudia has a Masters Degree in Business Administration and a Diploma in Educational Psychology. In her spare time, she listens to audiobooks and is an amateur genealogist. View her profile here.