Excel Lookup/Search Tip 6 - Vlookup with a Drop Down Menu and Data Validation
HTML-код
- Опубликовано: 11 сен 2024
- Excel Courses: www.teachexcel...
Excel Forum: www.teachexcel...
Excel Tutorials: www.teachexcel...
Learn how to use the VLOOKUP function in Excel in conjunction with a data validation drop down menu in order to create more logical and easy to use Excel spreadsheets. This allows you to use a drop down menu to supply the lookup value to the vlookup function in Excel instead of having to type the lookup value by hand. This reduces the amount of errors when searching a database and increases efficiency in Excel.
For Excel consulting or to get the spreadsheet or macro used here visit the website www.TeachExcel.com There, you can also get more free Excel video tutorials, macros, tips, and a forum for Excel.
Have a great day!
You are an absolute legend! I had an assignment that I could not find the answer to. I spent HOURS searching on how to do it. You are an absolute blessing, dude. Hope you reach your dreams in life!
I almost gave up hope. You explain it so easily! I was spending 4 hours yesterday to do that. You're awesome.
I have spent YEARS trying to figure this out, and you did it in 5 minutes. I cannot tel you how much I appreciate this video!?! You saved me on a job!
Spent all night watching through websites and youtube could not get my spreadsheet done until I came your tutorial! Many thanks for the effort!!
Damn....after a lot of search....I hit the right video ...didn't realise it would be this easy. Subscribed !!
Thank goodness I keep your video in my saved list, you helped me out many times over the years as I never remember how I made up the drop down list, thankyou
dis dude man deserves a medal
Thank you so much! I had wasted precious 5 hours earlier but with your video, I fixed it under 10 minutes! Thanks a lot!
I had been trying to figure this out for two weeks for a personal project. This is awesome!
This was so very helpful thanks so much, since my stroke I have forgotten most of the excel stuff I used to use in the past. So thanks very much.
Thank you you saved me
You're very welcome)
This was exactly what I needed!!!! Thanks sooo much.
THANK YOU, THANK YOU, THANK YOU! You made me look like a rock star at work!
Thank you very much for this tutorial, very helpful indeed!
You're God sent. Ive been trying to figure how this work at my job. Thanks!
9 years after and this still helps :D
I am going to through my old worksheet away immediately , thank you Sir !
2022 and it's still useful! Thank you ♥️
Great lesson, you are a help to us all!
God bless your soul for this
Old but very useful. I appreciate.
Okay. I have been working for two days on this excel document, and I could not get it to work. I've watched so many tutorials. I even broke my data sets into separate tables. Finally your video gave me the missing link. Turns out I was referencing the wrong cell in the first VLOOKUP argument. Thank you so much for your vid!
This was really helpful--thanks! One thing I would add is that you have to make sure the formula calculation option is set to "automatic". The additional cells (columns) won't change to correspond with the list if the calculation option is set on manual.
Thank you! I was going through a lot of vids when finally yours helped me!
P.S., you are a GREAT teacher!
Thanks, this made things easier.
You don't know how much you help me today.. thank you
Glad to help :) Don't forget to share with your friends)
Love you man, you are the best. Saved me a heck of time.
Thank you VERY MUCH for this video, I'm programing a bridge design tool in excel and this makes my work much more easier.
Thank you for the refresher!
@TheDarkemotion21 I'll answer all of your questions on the posts here. Vlookup will not by itself change any data. Once you pull up an employee record you can't use Vlookup to update the email address or any data. The type of system you are talking about will need to work through a Macro or system of Macros. Also, Vlookup doesn't 'like' multiple occurances in a list and won't easily allow you to move between multiple occurances.
Private Message me if you want macros made for you.
First of all thank you for your video. it really help me in my project of developing a form.
Thank U so much, this is what ive been looking for 😊
Wow... a big THANK YOU for the tutorial! This is exactly what I was looking for.
Glad it helped!
thanks! i thought i had to do macros and whatever not for this, this was so much easier!
The best tutorial! Thanks a lot.
First off thank you for all your advice and effort, its greatly appreciated. I've read through this section but have not quite found / understood what I need for my "issue".
I am trying to create a pay sheet for my staff. We have various staff on different pay grades. We have a separate excel sheet with all our staff names, numbers, paygrade, hourly overtime rate and various other details in rows. e.g. A2=name, B2=number, B3=rate, etc.
I would like to, using a drop down list, select the staff member by name and then in the subsequent columns have the required information (number, rate, overtime rate) inserted automatically in the subsequent columns in the pay sheet. e.g. in C11 we select the staff member by name. Once selected we require C11, C12, C13 and C14 in that sheet to be filled with the corresponding information of that staff member.
The rest of the paysheet is fine, it calculates the remuneration with no problems.
Could you also include the formula for getting the data from a separately saved workbook (we have the staff list stored in the cloud). Can you use a cloud link instead of a local path?
Thanks in advance!
joopz0r You should go to www.excelkey.com and ask on the forum there. It will be much easier to help there.
EASY TO LEARN
Gah this video is ten years old but it still helped! so much easier now thank you!
You made it simple thanks.
Thanks a lot ... Clear Explanation... Right on the money ! .... Well Wishes
Thanks
excellent - thank you.
Excellent!!
Thank you so much for this video!
YEESSSSS! Exactly what I was looking for, thank you so much!!!!
You're welcome! Glad to help :) And don't forget to subscribe for weekly updates!
Thanks for getting right to the point... seriously :) This helped me a lot.
GOOD TUTORIAL
THANKS.
Good tip about vlookup thanks teacher.
greeeeeeeeat video! THANK YOU!!!
So helpful!
Hey i am loving the videos, really helping me with school. I have a problem, not sure if you have a video on it I have searched for about a hour. basically my spread sheet has a whole range of pipe dimensions. They are displayed by name in a data validation table, when I choose one it works fine, the dimensions are displayed in the required location. I then have a range of equations using these values to display a total stress that the pipe is under.
Thank you!
this is great thanks it helps me...
Is there a video that would show how to do a dropdown to select a category in first column, then another dropdown to select an item in that category in column two, then finally return a value in the third column? Thanks
I just love you
Hey can you teach me how to do vlookup from 2 drop down menus ? for reference, I want to create a drop down menu on product as well as on stock.
The help I want is to connect both. If i select from product drop down menu it gives me value of stock , and when i select from stock drop down it gives me value of product. Both in same worksheet and same table. I want to link stock names and their ticker codes. for eg. Microsoft with MS. if i select Microsoft it shows MS in other box. and viceversa
Thanks alot
Let’s assume we need to add a new product or two.
Would the drop down update itself to then show the newly added products or not ?
If not what can we do so the drop down automatically updates itself should new products be added.
Thanks.
This is great!! I however am still on 2010 because my company will not move forward so i do not have the FILTER function that 360 has, is there a work around for seaching the contents in the dropdown box?
Hi there, Thanks for video. But I have a question that what about if want to look for price instead of stock? Can we make search a price as third drop down? Thanks.
Super clear instruction, but I get an error message every time with the lookup_value in bold.... Must be doing something really stupidly wrong though I follow the instruction to the letter! Any clues? Cheers
Wonderfull and thanks
this is great tutorial!! but could you please let me know also how to show up for the exact price
How do you do this with another workbork
Thanks for your time & effort! I have a question. When the dropdown "product" menu cell is empty, does the "stock" cell show "#N/A" on yours? If so, is there anyway to keep the "Stock" cell empty till the dropdown cell is populated? Thanks in advance!
I need to know, is there a way the spread sheet can evaluate all the dimensions in the data validation tab, then display all the pipes that have a total stress of less then a set value? Many thanks for your time.
Do you have a video for the same formula, but if the products are on a different sheet?
So many vlookup formulas and data validation tutorials and drop down menu tutorials. Search my channel on here or teachexcel for them and I'm sure one of them will help)
thank you so much
perfect....thankx
combinatorial problem -- given the 24 numbers taken 5 at a time, which unique combinations add up to 200?
I need to create a product list that has a lot of add-on options and I need to have the total price of a configuration. For example I have a first column that I select the item type (one costs 10, one 12 and one 15 euro). Then I have a second column that has add-on options for the item and it increases the price by 2,3 and 4 euro respectively according to what you choose. I need to add all the add-on values into one cell to show the total price. Do I still use the VLOOKUP function? Thanks in advance.
Can you set up a macro to run through the items in the data validation and then copy paste value from C2 onto another page? I am building a model with several inputs based on a product. I want the model to run through all 200 products I have and spit out the profit number. I have everything built, now I just need the macro to run through my data validation and for each selection record the profit number on a "summary" page. Any help would be great! Thanks!
Hai.. Is there tutorial for do in the source of dropdown list in other tab?
Thanks for the detail sharing with US, I would like to know the data basically Product number is Unique but in case Column H stock data may be 2 different types of number for example, 22 & 25 stocks with the same product number then how can i see the number in different column C ? please reply me asap
I would like to be able to enter a part number which in turn will trigger my drop down box to filter out all other part numbers. Can this be done?
awesome!
which video to learn how to do this between different worksheets?
Is it possible to do this for a drop down list which has its source in a different workbook ,
omg thank youuu!
Hi! Can we make a drop down list using indirect formula based on a vlookup value? for Ex. =Indirect(Vlookup.....) making it a dynamic list dependent?
I need to figure out how to make the column index change based on another drop down menu.
ok what if i had many products which are similar, how can they be added directly?
i wish to have different price lists for different client how can i do that
But is it possible to put search box Incase the list is plenty let say 120
hai dmin can yo show how to do the same thing but it involve different sheets?
Nice
Thanks for the video. I have a question though. What do i do when i encounter N/A for a particular criteria even though everything is there and selected beforehand? Thanks in advance for the reply
It sounds like maybe you need to clean your data a bit. Sometimes data in Excel looks different than it actually is within the cells. Upload a sample file with your question to our forum and we can take a look! www.teachexcel.com/talk/microsoft-office?src=yt
what if the table contains 25,000 lines? vlookup kills the memory on the pc when performing this task
How can I add color to the vlookup
Your a god send lol
haha, I accept offerings of Gold to save your soul! I'm glad you liked my tutorial :)
hi there, i would like to do this steps in another way round, i need to do a vlookup from a dropdown list in different worksheet and i can't figure it out, pls help? thanks :)
Can totally help, just upload a sample file with your question to our forum and we can get you sorted! It seems like maybe you will need to have an additional cell/table to get the desired results. Forum: www.teachexcel.com/talk/microsoft-office?src=yt
Hi, thanks for this tutorial.
I am having some issues with my VLOOKUP, I am trying to surface the contact number of the chosen user in my dropdown list.
I've selected User 1 expecting to see contact number 1 but the vlookup brings up the results of my 3rd row instead.
Can you help?
how about 2 column in 1 ?
Not sure exactly what you're asking, but to put 2 columns together, just use concatenation like: =A1&A2 and you can search on that. If you like, you can ask on our forum and upload a sample file for more specific help: www.teachexcel.com/talk/microsoft-office?src=yt
Thank you! Video was very helpful.
Thanks so much!
thankyou so much!
Thanks
Thank you!
Thank you sir!