A common requirement in spreadsheet work is to create a drop down list in Excel and based on the chosen item, lookup another item. The way to do this would involve a tool and a formula. The tool will be the Data Validation tool and the formula that will help is the VLOOKUP formula
Build a Drop Down list in Excel
The correct tool is the Data Validation tool. So in the Example below we want to have a drop down in the blue cells (1) that uses the list in F6 to F14.
The process would be to click on the cell (or cells) were you want the dropdown (1), click on DATA (2) and DATA VALIDATION (3). In the Allow option (4) choose List and then in the SOURCE box (5) link it to you list. Note that we only highlight the F column containing the names. We will pull through the departments in the next step.
Lookup more information based on the dropdown selection
Now you want the department to immediately be pulled through based on the name you select in the dropdown.
For this you need a formula and the correct formula for this is VLOOKUP. VLOOKUP performs a Vertical lookup by finding the matching name and pulling back the associated department. If you are unfamiliar with VLOOKUP look at the Preview Lessons in the VLOOKUP course.
As shown below, in cell B6 we have created a VLOOKUP that
- Uses the name chosen from the dropdown as its LOOKUP VALUE
- Refers to the table in $F$6 to $G$14 (note that it can be on another sheet, the lookup value MUST be in the first column shown and you must highlight all the columns you may want to bring back).
- We want column 2 to be brought back (note how Excel will count the columns- it is not the 7th column as it is column G but the 2nd column of the TABLE ARRAY).
- We specified FALSE which means that Excel must either find the exact match or else return an error message to tell us that the chosen name is not in the list.
Now you will be able to choose a name from the dropdown list, and the formula will automatically pull through the correct department.