Prompt User for Input for value in lookup table

Copper Contributor

=IF(A20=1,VLOOKUP(B20,'Course Offerings'!$A2:$B103,2,FALSE))


For the above lookup formula in Excel, I have a 2nd 2-column worksheet. It contains the Course Name in Column 1, and the Course Credits in Column 2. The trouble is that some courses have variable credits instead of a fixed number. How can I insert a place marker in the 2nd worksheet cells that amend the above formula to prompt the user for the variable credit amount?

For example, if an internship course has 1-6 credits for the course in the 2nd worksheet, how do I let the formula for the cell  in the 1st worksheet ask for the specific credits for the course?

1 Reply





If you need a Prompt, you probably have to write VBA.

but if you only need a drop down list,  there is some Excel built-in function you can use. (I attached an example spreadsheet):


e.g. for this "Internship" course, you can input "1,2,3,4,5,6",

Screenshot 2023-11-13 at 11.19.35 AM.png


Then in your first sheet, you can use "TEXTSPLIT" to create a drop down list:


Screenshot 2023-11-13 at 11.27.09 AM.png