VLOOKUP for multiple options influencing Gantt chart

hcosta52671 ✭✭✭✭
edited 12/09/19 in Formulas and Functions

I have a sheet that uses Gantt chart data to show assignments on a calendar.

The persons assigned are all in a "Name" column that is a drop down with all the employee names on them. Next to that are start and end date columns that then show a bar range in calendar view.

The employee's name will only appear in the bar in calendar view if the name is in the Primary Column.

The Primary Column can only be text, and NOT a drop down list.

I have been successful in using VLOOKUP so that when the drop down name is chosen in the Name column, that name populates into the Primary Column.

My problem, is that I cannot figure out how to do that for multiple choices. (For example, if in my drop down I have employees Larry, Moe, and Curly, my VLOOKUP works for only one of those names.) Is there a way to use VLOOKUP so it will return the correct name for any employee chosen? 


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Is the dropdown list on the same sheet? Are you able to provide some screenshots?

  • hcosta52671
    hcosta52671 ✭✭✭✭

    Yes. The drop down is on the same sheet. It's the name column in the screenshot.

    The ID column is the primary that I want populated with my choice from the drop down.

    My goal is to be able to easily build an on-call calendar. The drop down populating the primary column would cut down on typing.

    The only way I've gotten it to work, as the screen shot shows, is that each row has the specific formula identifying that person.

    So, in row 1, if I chose "Nick" or "Kira" it would give me a #NO MATCH error in the ID (primary) column.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Would it work to just use a basic




    to pull the name from the Name column into the Primary Column? What is the reason for using the VLOOKUP?

  • hcosta52671
    hcosta52671 ✭✭✭✭

    Holy cow!! <face palm>

    That did exactly what I wanted it to do!

    I was using VLOOKUP because that was the only formula that I could see that would do what I wanted to.

    Thank you very much! You've earned your gold star for the day!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! There are actually a handful of formulas that can be used to pull data.


    There is a template in the solution center that contains examples of all of the different functions. It is totally interactive, and if you accidentally mess something up, you can delete the sheet and download a fresh template. See the snippet below.

    Formula Examples.PNG

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!