Forum Discussion
Can I Use the VLOOKUP Formula to Return a Data Validation Drop Down List?
No, you can't use VLookup, but you could still add data validation. The caveat is that you can't have user input and a lookup formula in the same cell, so you would need to add a column for user selection from the data validation list. Then, using an IF formula in the instructor field, you can perform a lookup when there is only one instructor, or pull from the user's selection from the data validation when there is more than one.
Thank you for your reply. I don't think your suggestion solves my problem, but what I'm hearing is VLOOKUP can't be used to return a whole data validation list. I'll just have to change each entry that had multiple possible instructors manually. Thank you again for your time.
No, you can't use VLookup, but you could still add data validation. The caveat is that you can't have user input and a lookup formula in the same cell, so you would need to add a column for user selection from the data validation list. Then, using an IF formula in the instructor field, you can perform a lookup when there is only one instructor, or pull from the user's selection from the data validation when there is more than one.
JMB17 Greetings, I am trying to do something similar. However, I don't need the vlookup column, because I always want the user to select an entry. But I want that data validation drop-down list to change based on a selection in the previous column as this one seems to do. I have changed the named formula (VersionList) to reflect the table and field names in my example, but I only get a message that the "Source currently evaluates to an error" I think it has something to do with my lack of understanding of how the $F12 reference in your named formula works. Can you tell what I am doing wrong?
- I think the issue is just an incorrect cell reference in the match formula - it's referring to cell $F1048574. Try this for your VersionList formula:
=INDEX(TableBudgetVersions[BudgetVersion],MATCH(TableVersionProductions[@SeasonNo],TableBudgetVersions[SeasonNo],0)):INDEX(TableBudgetVersions[BudgetVersion],MATCH(TableVersionProductions[@SeasonNo],TableBudgetVersions[SeasonNo],1)) - Oh. That's a new one on me. Thanks. I think I'll go with JMB17's response below because it doesn't require the use of a "helper range," like B23. But it's good to know how to make this kind of reference. I kind of migrated all of my work that needed to manipulate ranges to Sheets a bit ago, because their methods seemed more straightforward to me at the time. Now I have fallen behind on how to do that in excel. Thanks to both of you for the pointers.
- Thank you! This seems like a great alternative.
It can help you https://tinhocvanphong.net/huong-dan-su-dung-ham-vlookup-trong-excel/
