SOLVED

Can I Use the VLOOKUP Formula to Return a Data Validation Drop Down List?

%3CLINGO-SUB%20id%3D%22lingo-sub-2023565%22%20slang%3D%22en-US%22%3ECan%20I%20Use%20the%20VLOOKUP%20Formula%20to%20Return%20a%20Data%20Validation%20Drop%20Down%20List%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2023565%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20wondering%20if%20it's%20possible%20to%20use%20the%20VLOOKUP%20formula%20to%20return%20an%20entire%20Data%20Validation%20List%20(with%20all%20possible%20options)%2C%20not%20just%20the%20value%20that%20is%20currently%20selected.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20example%20I'm%20using%20VLOOKUP%20to%20automatically%20fill%20in%20the%20instructor%20names%20of%20various%20courses%20by%20reading%20the%20entered%20course%20name%20(Column%20F)%20and%20returning%20the%20corresponding%20instructor%20(column%20G)%26nbsp%3Bbased%20on%20the%20course%2Finstructor%20list%20(Table%201).%20This%20works%20for%20all%20courses%20with%20only%201%20possible%20instructor%2C%20but%20not%20with%20the%20%22Math%22%20course%20which%20has%20three%20possible%20instructors%2C%20which%20I've%20captured%20using%20a%20Data%20Validation%20List.%20Is%20it%20possible%20to%20use%20VLOOKUP%20to%20copy%20the%20whole%20list%20of%20the%20three%20possible%20instructors%20so%20that%20I%20could%20choose%20which%20one%20taught%20the%20course%20on%20a%20particular%20day%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2023565%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2023718%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20Use%20the%20VLOOKUP%20Formula%20to%20Return%20a%20Data%20Validation%20Drop%20Down%20List%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2023718%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F813675%22%20target%3D%22_blank%22%3E%40NeilBost%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVLOOKUP%20only%20returns%20the%20first%20instance%20in%20the%20match.%20A%20better%20alternative%20is%20to%20create%20a%20new%20column%20in%20Table%201%20that%20can%20be%20used%20a%20reference%20for%20future%20lookups.%20This%20can%20be%20a%20Unique%20ID%20for%20the%20courses.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20when%20referencing%20Courses%20in%20Table%202%2C%20the%20Unique%20ID%20will%20always%20return%20the%20respective%20instructor%20even%20if%20the%20course%20list%20is%20repeated%2C%20since%20the%20Unique%20ID%20is%20unique.%20That%20is%20the%20essence%20of%20working%20with%20relational%20databases%20in%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2034351%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20Use%20the%20VLOOKUP%20Formula%20to%20Return%20a%20Data%20Validation%20Drop%20Down%20List%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2034351%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F867265%22%20target%3D%22_blank%22%3E%40adversi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%20I%20don't%20think%20your%20suggestion%20solves%20my%20problem%2C%20but%20what%20I'm%20hearing%20is%20VLOOKUP%20can't%20be%20used%20to%20return%20a%20whole%20data%20validation%20list.%20I'll%20just%20have%20to%20change%20each%20entry%20that%20had%20multiple%20possible%20instructors%20manually.%20Thank%20you%20again%20for%20your%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2034807%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20I%20Use%20the%20VLOOKUP%20Formula%20to%20Return%20a%20Data%20Validation%20Drop%20Down%20List%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2034807%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F813675%22%20target%3D%22_blank%22%3E%40NeilBost%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%2C%20you%20can't%20use%20VLookup%2C%20but%20you%20could%20still%20add%20data%20validation.%20The%20caveat%20is%20that%20you%20can't%20have%20user%20input%20and%20a%20lookup%20formula%20in%20the%20same%20cell%2C%20so%20you%20would%20need%20to%20add%20a%20column%20for%20user%20selection%20from%20the%20data%20validation%20list.%20Then%2C%20using%20an%20IF%20formula%20in%20the%20instructor%20field%2C%20you%20can%20perform%20a%20lookup%20when%20there%20is%20only%20one%20instructor%2C%20or%20pull%20from%20the%20user's%20selection%20from%20the%20data%20validation%20when%20there%20is%20more%20than%20one.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I'm wondering if it's possible to use the VLOOKUP formula to return an entire Data Validation List (with all possible options), not just the value that is currently selected.

 

In the attached example I'm using VLOOKUP to automatically fill in the instructor names of various courses by reading the entered course name (Column F) and returning the corresponding instructor (column G) based on the course/instructor list (Table 1). This works for all courses with only 1 possible instructor, but not with the "Math" course which has three possible instructors, which I've captured using a Data Validation List. Is it possible to use VLOOKUP to copy the whole list of the three possible instructors so that I could choose which one taught the course on a particular day?

5 Replies

@NeilBost 

VLOOKUP only returns the first instance in the match. A better alternative is to create a new column in Table 1 that can be used a reference for future lookups. This can be a Unique ID for the courses.

 

Now when referencing Courses in Table 2, the Unique ID will always return the respective instructor even if the course list is repeated, since the Unique ID is unique. That is the essence of working with relational databases in Excel.

@adversi 

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.

best response confirmed by NeilBost (New Contributor)
Solution

@NeilBost 

 

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! This seems like a great alternative.

It can help you Hàm vlookup trong excel