Drop-down lists in Excel are much like the ones you often see on web-based forms, where you can choose only from a fixed list of choices. The reason these are useful when it comes to vlookups is that they limit the possibility of errors and “N/As” that you can get when using the vlookup function.
Let’s go back to the first tutorial I wrote on vlookups, in which given a name of a pet, you used vlookup to find the type of animal it was:
As you can see, the names of the animals are listed in lower-case. If someone were to lookup the name “fido” by typing “Fido”, vlookup would conk out and give an error message, because it’s sensitive to capitalization. Or if someone were to make the easy mistake of typing “Mickey” (as in mouse), instead of “Mikey”, vlookup would again conk out.
So, what to do?
Enter a dropdown list. Creating a dropdown list in Excel is easy, though it suffers from the limitation that the list of choices allowed has to be located in the same tab as the dropdown list itself. Why? I have no idea.
Here’s what we’d do in this case.
Step 1: Create the list of possibilities somewhere on the same tab as where the drop-down list will be located. Let’s suppose we want to put the drop-down list somewhere near column A and B on “Sheet 2”.
In this case, we have a simple situation, so I’ve put the list in column G. I’ve decided to sort the possibilities in alphabetical order (you can do this easily by highlighting the list and then hitting the “A-Z” button with the downward arrow on the toolbar menu or just do it manually since we have 4 choices):
Step 2: Next, suppose we want to put the drop-down list in cell B1, by “Name”. Highlight cell B1 and go to Data > Validation. A pop-up window will appear:
Step 3: Under “Settings” and “Validation criteria”, under “Allow:”, choose “List”:
Step 4: Under “Source” select the values you put in column G:
Step 5: Hit
Now you can toggle between the four choices of “fido”, “lassie”, “mikey”, and “whiskers” only:
If we put our formula to vlookup the animal type in cell B2 now:
(this assumes that the original table is in Sheet 1 and between cells A1 and B5), then you will see the animal type change as you change the name listed in the dropdown menu in cell B1:
One last thing (which is a bit of a kluge). To keep your excel sheet nice and tidy, you can always hide the list of names that we put in column G by changing the font to white. That way, the list is essentially hidden (unless of course you change the color of the cells!)
Hope this tutorial’s been helpful. Excel actually has lots of other web-form-like features to it like radio buttons and the like that I might cover in a later post.