This article is part of our VLOOKUP Week. Read more.
Situation
Sometimes we don’t know what we want. If this happens when I am in a bar, I usually order a cocktail. Just a mix of everything. The same will work in Excel too.
For eg. If you have lots of data, but the value you want to look up needs to change based on whims and fancies of your users, then you can resort to a cocktail. A mix of VLOOKUP with Drop down lists (Data validation)
Data:
Solution
The recipe for VLOOKUP cocktail is relatively simple. We just take the list of sales person names and use it as a source for our input cell’s data validation drop down list. Rest is left to your imagination. Here is an example in action.
Examples:
Sample File
Download Example File – Mix VLOOKUP with Data Validation for Some Magic
Similar Tips
- Excel Basics – Add Drop Down List using Data Validations
- Generating Invoice Numbers in Excel – An example of VLOOKUP use
- Excel SUBTOTAL Formula – What is it, how to use it & examples
3 Responses to “Top 5 Excel Skills you need to be a Successful Data Analyst in 2023 (podcast)”
Thanks Chandoo, some great helpful tips here. I'm quite good at Excel (mainly thanks to you!) but I'm always learning. I love the link you gave above to Dynamic Arrays & Spill Ranges, I understand them a bit more now, and can see some uses for them.
I am glad you enjoyed the show Dermot.
Great to see you back! Enjoyed when you first started the podcast and super excited to start listening to you again!