There are several tutorials out there on how to create a drop down list. Here I wanted to show how to combine tools and functions together in different ways to make new and more useful tools.
As a Microsoft Access man, I get very frustrated with the way Excel looks up data from a spreadsheet. I just want to grab a value based on the field name of a record set. How hard is that?! Why can't Excel just look up my bleepin' record!? Well, Excel IS NOT A DATABASE!!! Yet there are those that like to use it as one, and those that have to cater to them. With this in mind, we will build a simple vlookup that will find a value from the column based on the value of the first row.
- VLOOKUP – Looks in the first column of an array and moves across the row to return the value of a cell – Read more here
- MATCH – Returns the relative position of an item in an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself – Read more here
This tutorial was written for Excel 2007.
Find more information about Microsoft Excel functions at the Microsoft Office website.
The first sheet we will simply name "Favorites" with the below layout.
The second sheet, "Book List", will have the data we will be looking up.
|2||Atlas Shrugged||Ayn Rand||Random House||12-Oct-57|
|3||The Stand: Complete & Uncut||Stephen King||Doubleday||1990|
|4||State of the Union: A Thriller||Brad Thor||Atria||1-Jan-04|
|5||Dune||Frank Herbert||Chilton Book Company||1965|
|6||Complete Sherlock Holmes||Sir Arthur Conan Doyle||Doubleday||20-Apr-60|
First, lets make it easy to choose a book from a drop down list. We will select A2 through A7. I always grab one extra empty row when creating named ranges. This is so I can insert a new row at the bottom and keep the above data in the same order. I have also seen people insert the new row at the top every time to make the list in reverse order. It's really just personal preference. We will name this range "book_titles".
Next we will go back to the "Favorites" sheet and select cell B2.Select the "Data" tab from the ribbon.
Click on "Data Validation".
This will bring up a window. From the "Allow:" drop down, select "List". This will change some of the fields in the window. Under "Source:", type in "=book_titles".
Click on OK and a little drop down button will appear next to the B2 cell.
With B2 selected, drag down to B6. This will create drop downs for all cells within the range. Try selecting some books.
Back on the "Book List" sheet, highlight all the book information. Name this "book_info".
Return to the "Favorites" sheet and in cell C2 type the "=VLOOKUP(B2,book_info,2,FALSE)" function. Make sure FALSE is placed in the last variable, otherwise strange data may appear in that cell.
In cell D2 type "=VLOOKUP(B2,book_info,3,FALSE)". Drag the functions down to row 6. This simple VLOOKUP will return values based off the items selected from the drop downs in column 3.
This is all great, but what happens if a column is inserted, or the columns are re-arranged?
It breaks the tidy code that does our look up. So how to fix this?
On the "Book List" sheet one more time, highlight the items in the first row that are being used as column heads.Name this range "book_columns".
On the "Favorites" sheet, modify D2 with "MATCH("Publisher",book_columns,0)". What this does is find the word "Publisher" from the cells in the "book_columns" row and returns a numerical value for how many columns from the left the value appears. Like with VLOOKUP, the MATCH function has a third variable that must have a value. In this case it is "0" so an exact match will be found. Why "exact match" isn't the default value saving thousands from extraneous key strokes I couldn't tell you... seems a common theme with Excel.
Insert the MATCH function into the VLOOKUP function in the C column as well. Drag the new formulas down to update the other cells.
Now move or insert columns on the "Book List" sheet and the functions on the "Favorites" sheet do not need to be updated to keep up.