Excel 2010: Create drop-down list and VLOOKUP

Excel allows one to use the Data verification as well as a Drop-down list to create. This is to facilitate data entry and to limit the entries to previously defined elements. There are numerous application examples for the function. For example, you can equip the address lines of an invoice with the data tool and change the recipient with a simple click.

Here was chosen for a Example decided in which the most successful via a drop-down list Soccer nations can be selected. On the VLOOKUP after the selection, the Quantity , the World title of the respective nation appear.

1. Create a drop-down list

First of all, a List created that includes all elements. This can also be done on another worksheet.

Now you select a cell in which the elements are to be made available via the drop-down list. Under the tab Features can be found in Ribbon the data tool Data verification.

A window opens in which under the tab Settings Taj Validity criterion List is chosen. To the Source select, click on the small button on the right and in this case mark the nations.

Once you have marked the source, click the small button again and confirm with OK. The Result then looks like this:

 

2. The VLOOKUP function

 

If you want to assign further properties to the respective selection from the drop-down list, this is done with the VLOOKUPOther specifications possible. So it can be realized, for example, that when a nation is selected, the number of world championship titles is automatically output.

First, of course, a table must be created with the data. It is wichtig, that the Table which serves as a source, right-wing from the inserted VLOOKUP lies.

Now you select, for example, a cell under the already completed drop-down function, and add it there using the tab Formulas > Other specifications insert the material moisture meter shows you the VLOOKUP .

The next thing to do is Function arguments can be determined immediately.

 

 

Search criteria: The cell that can be changed using the drop-down list.

matrix: The table with the source data.

Column index: The column number within the selected matrix to be used.

Area_referencing: Wrong means that a 100% match is only searched for.

As a end result you then have the following view:

With the selection of the nation, the number of world championship titles also changes automatically.

This simple example is intended to illustrate how versatile the two functions can be. Both functions can of course also be used independently of one another.

 

___________________________________________________
This tip comes from www.win-tipps-tweaks.de
© Copyright Michael Hille

Warning:
Using Registry Editor or its tips incorrectly can cause serious system problems that may require you to reinstall your operating system. Tampering with the registry files and using the tips is at your own risk.