Forum Discussion
Data Validation for Multi-Column Selection in Drop Down List
Thanks for all of your helpful suggestions, but I was able to get what I wanted to do with the following formula:
=IFERROR(VLOOKUP(A2,Sheet1!A2:B,2,FALSE),"Choose a Food")
You're certainly welcome. I'm glad you found a solution that is working for the time being.
I'd have to say, it looks like something that can function, but may not be the most effective in the long run. One of the fascinating things about Excel is that there often are multiple routes to a workable solution. Sometimes they're equally effective; other times they're not.
So I'd still invite you to post your spreadsheet and solicit feedback....... along with a full description of the context here. How is this being used?
I'd like to see if there are any other ways to make this multi-column drop down list bring over two columns of data using a Data Validation List.
I've attached the example of what I've been trying to accomplish.
Thanks for you help!
Here's another formula for your Results sheet. Enter this in Cell B2 and copy it down...
=XLOOKUP(A2,Sheet1!$A$2:$A$7,Sheet1!$B$2:$B$7,"Error",0)
However, you still haven't explained the full context here. I suspect that you're not actually dealing with fruits and vegetables (or is Avocado actually a fruit?)...anyway, maybe the actual application is confidential, so fine.
The XLOOKUP function is relatively new, and more flexible/powerful than VLOOKUP and the other associated LOOKUPs from long ago.
I have a spreadsheet that creates a secondary dropdown that changes based on what's entered in the first column. There's a dependency, in other words. I don't think that's what you're seeking--doesn't seem to be what you've described--but I'll attach a generic example of that.
The functions in the DataValCascading do require the most recent release of Excel, by the way.
The spreadsheet data is only an example to simplify things.
The actual data is hiking/backpacking gear and consumables in Column A, and the weight of items in Column B.
I use two sheet tabs. One for days hikes and one for backpacking. I also have another sheet tab for all gear and consumables, where I get the data from.
The items I bring for either hiking or backpacking vary with the change of seasons. I also am very critical to bring the minimum amount of weight required for a safe trip, so I list and go over weights thoroughly.
I have a considerable amount of experience, so I know what to bring. I'd just like to select items as needed from a drop down list that would bring the weights over as well and have the totals auto-populate.
I did try my revised spreadsheet with the XLOOKUP. However, when I tried using the drop down list, the data in Column B changed to #NAME?
I also tried the other spreadsheet you attached, but the drop down menu with the names wouldn't open or show anything so that I could select one of them.