Recently, I was helping a reader with an Excel-based tool for his business that involved some vlookups for which using a drop-down list in Excel proved useful.
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:
and hit
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.
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
Flexo
Here’s a suggestion replacing using white text. Your valid values can be stored in a separate worksheet hidden from the user (Format -> Sheet -> Hide). The sheet can be “unhidden” if necessary but it keeps everything else “clean.”
ricemutt
Thanks Flexo. The problem is that using Data > Validation to create a dropdown list specifically prevents you from putting the selected values in a different worksheet. It has to be on the same tab as the actual drop-down list itself, hence leading to the kluge I mentioned :) I’m not sure why Excel won’t let you populate values from a different tab with this method….
Dave
not if you name the list in the other tab, and then use Allow -> List in the validation dialog, then typing =NameOfyourList
(i’ll let you figure out the exact details for a new post!)
Ricemutt
@Dave, you’re probably right. I tend to shun using named ranges/lists :) but in this case it might come in useful!
Ajeesh Nair
It was a great help. Thanks a lot!!!!
Marnie Lewis
I have set up a drop down list and it was working fine but now the drop down list doesn’t appear when you are in the cell. It still limits you to the terms on the list but doesn’t show you what they are. I have made sure that the check box is ticked for “in-cell dropdown” but it still won’t show. It has done this for all the dropdown menus in this worksheet. Please help!
Emily
Thanks of your tutorial, is really help me!!!
David
You can just type the constants separated by a , sign on the source box instead of typing a range and you would be OK without hidding anything.
Brandi
Thank you so much for this! I’ve been trying for hours to figure out how to do it!! I love Google search!!!
Mohammed Ikram
Thank you for the help you have provided, I want to ask, How to see the pictures? when we have entered name in one column and inserted pictures against the names then how to use vlookup in list.
Jigmie
Thanks for the hints!!
anand lokhande
very helpful information. Always updates helpful tips.
Thanks
Bob Bishop
I would like to create a drop down list and use the vlookup to return a numerical value for each drop down list item. i.e. the drop down list would be
appointments
referrals
sales
where if you select one of the items in the drop down list it returns a numerical value of 1 for the appointments, 2 for referrals, and 3 for sales, for which the numerical value is placed in the next cell over so the numerical values can be totalled at the bottom of the column. How do I do this. Thanks.
Bob Bishop
I would like to create a drop down list and use the vlookup to return a numerical value for each drop down list item. i.e. the drop down list would be
appointments
referrals
sales
where if you select one of the items in the drop down list it returns a numerical value of 1 for the appointments, 2 for referrals, and 3 for sales, for which the numerical value is placed in the next cell over so the numerical values can be totalled at the bottom of the column. How do I do this. Thanks.
james madeira
Thank you for your timely tutorial. I would be pleased and grateful if you could send me functions and formulas in excel that would suit my database of employees in HR department.
Hope I’m not burdening you.
Regards,
James
Tanvi Suji
I learn so many things thanku hope i will lear alot more things from it.
OTHMAN KOYAMU
A good simple laymen in mind tutorial steps. Thanks bro.
Paula
Thank you so much for such an excellent website, I found it when I was looking for an easy way to understand VLookups and found so much more information on excel that I could easily understand.
Andy
I know this is a bit late but I just found these tutorials and I think they are great! In the interest of accuracy, you CAN store your list in another sheet just type the sheet into the location. It would look like this =’Sheet1′!B2:B27 assuming your dats is in column B rows 2-27. Anyway, thanks for the tutorials!
Doll
GREAT POST!!! Very helpful…been trying to crack this for hours….your explanation was the most simple, concise and to the point. Keep up the good work.
Sneha Gulati
Thank you much
This has been a great help to me .
Joxer
thanks for this. really really helpful.
suresh chand
Thank you so much for such an excellent website, I found it when I was looking for an easy way to understand VLookups and found so much more information on excel that I could easily understand.
Prasoon
Very nice and very simple to understand… help me alot !
Prasoon
How to have a validation between two set of data from two coloumns !
ex If first column has 2 valid entries and from that one entry there are 10 valid entries in the second coloumn. How do we ensure that validation ?