Microsoft Excel

3 minute read

Excel Challenge 26: VLOOKUP With Duplicates

Deb Ashby

Deb Ashby

Join the Excel conversation on Slack

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

Ready for Excel challenge #26?

How well do you know VLOOKUP in Excel? If you are comfortable with VLOOKUP, then this challenge should test your "out of the box" thinking. 

Download your challenge data

Think you have the answer? Download the file and prove it!

Everything you need to participate in the challenge can be found on this page. To take part:

  1. First, watch the challenge video and read the instructions below the video.
  2. Review the previously published video(s) and article(s) on which the challenge is based.
  3. Download the Excel worksheet you will use to complete the challenge tasks.
  4. 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🧠

 

The scenario🖼️

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

The Sales Manager has contacted the HR Department requesting a list of all employees in the Sales Team and their salaries. He’s looking to conduct a pay review and wants to ensure employees in his team are being competitively compensated. 

It’s our job as the HR Manager to extract the salaries for the members of the Sales Team and send them on to the Sales Manager. 

However, we have a challenge! John Smith is a very common name. In fact, there are three employees called John Smith in the list. 

When we use VLOOKUP, it returns the wrong result. Our challenge is to think about how we might extend the capabilities of VLOOKUP to achieve the correct result. 

The task🏋️

Write a formula in Column K that will look up the “Employee” in column I in the table and return the correct result. 

Excel challenge spreadsheet

 

The clues🕵️

Here are some things to consider:

  • Think about how to make each record in the table unique. 
  • Use a “Helper” column. 
  • Ensure the formula works when you copy it down column K. 

How quickly can you complete this challenge? ⏱️Fifteen minutes? Ten? Less?

Learn more about how VLOOKUP really works in the following video:

 

And pick up a trick or two from this recent article: Handling Lookups for Multiple Criteria in Excel.

If you’re already proficient with Excel, this should be quite easy. Maybe you can tell us a creative way to solve this problem in our Slack channel.

Have fun!


Download your challenge data

Think you have the answer? Download the file and prove it!

Tell us how you would solve it, then check out our solution below!


The solution💡

We hope you enjoyed taking part in this challenge!


Hungry for more?🥗

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.

Level up your Excel skills

Become a certified Excel ninja with GoSkills bite-sized courses

Start free trial

Join the Excel conversation on Slack

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

Deb Ashby

Deb Ashby

Deborah is a Microsoft MVP and TAP Accredited Microsoft Instructor and Content Creator. She has had various careers in the IT industry since the mid 1990s. This includes various roles in IT training, including the starting of her own company where she focuses on creating video-led, Microsoft training courses for clients. She is an instructor at GoSkills with various Microsoft courses, including Excel for Business Analysts. In her spare time, she likes to swim, practice yoga, travel, and refine her photography skills. View her profile here.