Validation + Vlookup = Power!

Sometimes the combination of two easy-to-use fundamental Excel tools can produce powerful results in your workbook. Using a DropDown Box in an Excel report can add interactivity, efficiency, and professional flair to your worksheet.

A very simple way to do this is by using Validation. In Excel 2007, simply select the cell in which you want the dropdown, (such as the cell F2 directly below Sales Rep in the above graphic), click on the Data tab and choose DataValidation. Then choose List and select a range for your Source of dropdown entries.

Alacazam! Instant DropDown Box!

Now you can combine the dropdown with the elements of a Vlookup function to create a powerful and interesting report in Excel. For example, using the table below, you can put the following Vlookup formula in cell G2 directly under the 2011 Sales in the above graphic:

=VLOOKUP($B$13, $A$2:$D$6, 4, FALSE)

As a refresher, here is how the 4 arguments of the Vlookup function work:

=Vlookup(lookup_value, table_array, col_index_number, range_lookup)

1. lookup_value: In this case the Sales Rep
2. table_array: Use an absolute reference ($A$2:$D$6), Note: Be sure not to use the header of your table.
3. col_index_num: In this example, the 2011 Sales is in column 4 of our lookup table.
4. range_lookup: If you want an exact match, you should use “False”

Validation + Vlookup = Power! Give it a try!