Excel Lookup/Search Tip 6 - Vlookup with a Drop Down Menu and Data Validation

Visit http://www.TeachMsOffice.com for more, including Excel Consulting, Macros, and Tutorials.

Learn how to use the VLOOKUP function in Excel in conjunction with a data validation drop down menu in order to create more logical and easy to use Excel spreadsheets. This allows you to use a drop down menu to supply the lookup value to the vlookup function in Excel instead of having to type the lookup value by hand. This reduces the amount of errors when searching a database and increases efficiency in Excel.

For Excel consulting or to get the spreadsheet or macro used here visit the website http://www.TeachExcel.com There, you can also get more free Excel video tutorials, macros, tips, and a forum for Excel.

Have a great day!

Category:

Education

Tags:

• microsoft excel 2007
• excel tips
• excel tricks
• excel guides
• excel tutorials
• excel help
• excel teacher
• spreadsheets
• worksheets
• workbooks
• functions
• formulas
• excel
• vlookup
• data validation
• drop down menu
• efficient spreadsheets

[TheDarkemotion21]

How would you set up the following

Imagine you wanted to search all the products that had stock at 36. How would you set up a vlook up to find the product number that has 36 (ESPECIALLY if there were 2 products at 36 )

[ExcelisHell]

@TheDarkemotion21 I'll answer all of your questions on the posts here. Vlookup will not by itself change any data. Once you pull up an employee record you can't use Vlookup to update the email address or any data. The type of system you are talking about will need to work through a Macro or system of Macros. Also, Vlookup doesn't 'like' multiple occurances in a list and won't easily allow you to move between multiple occurances.

Private Message me if you want macros made for you.

[gogaston]

I would like to be able to enter a part number which in turn will trigger my drop down box to filter out all other part numbers. Can this be done?

[37no37]

combinatorial problem -- given the 24 numbers taken 5 at a time, which unique combinations add up to 200?

[iambebeduckie]

Can you set up a macro to run through the items in the data validation and then copy paste value from C2 onto another page? I am building a model with several inputs based on a product. I want the model to run through all 200 products I have and spit out the profit number. I have everything built, now I just need the macro to run through my data validation and for each selection record the profit number on a "summary" page. Any help would be great! Thanks!