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
4 Responses to “Office 2010 Contest Winners are here!!!”
I while ago I wrote a post on selecting a couple of names from a range via an UDF
I could have been handy.... especially because I didn't win.... lol
http://xlns.lamkamp.nl/?p=14
Sweet! I won! Thank you so much, Chandoo! I'm really speechless! I'll look out for an e-mail from you. Again, I really appreciate it, and I can't wait to fire it up!
Sincerely,
Tom "this one" 🙂
Thank You... Thank You... Thank You... 🙂
Hi,
Don't want to ruin your party.. 😉 but I noticed that when you sort the list A2:B11 (step 2), the RAND function re-calculates the numbers so that they are different and in mixed order again. I had to paste the whole area as values first and then sort to get it to work.
Wonder if the same happened to you because in your list at least Greg has a higher value than Tom 🙂