VLOOKUP help formula

Hello,

I am having issues with my formula and I could use some help! I have one sheet that I added a column called "Source" that is already listed on another sheet. Sheet 1 has all of the contacts and their sources and Sheet 2 is the one used now to track the projects for each of the contacts.

This is the formula I have:

=VLOOKUP([Site Name]@row, {Source}, 5, false)

I cam trying to have the column list the sources for the corresponding "Site Name" referencing the other sheet. What am I doing wrong?


Answers

  • sharkasits
    sharkasits ✭✭✭✭✭

    @laura.sandoval I'm not sure if it's possible with a vlookup, but you can do it with a JOIN(COLLECT()).

    With what you have, it should be:

    =JOIN(COLLECT({Source},{Site Name}, [Site Name]@row), ", ")

  • =JOIN(COLLECT({Source}, {Site Name}, [Site Name]@row, ","))


    Thanks for the response! This is the formula I used and it's not working :( it says incorrect argument. Any thoughts?

  • Genevieve P.
    Genevieve P. Employee Admin

    Just hopping in here to help clarify!

    It looks like your comma is on the wrong side of a closing parentheses:

    =JOIN(COLLECT(---), ",")

    Cheers,

    Genevieve

  • Thanks Genevieve, still not working for me unfortunately.

    =JOIN(COLLECT({Source}, {Site Name}, [Site Name]@row, ","))

    This is what I have...where is the comma incorrect? Is this formula something I can even do?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @laura.sandoval

    I like to think of the Collect function like a filter - then the JOIN function brings all the values from your filter together, but you need to specify how they're split up (the comma) and you can only do that after closing out the COLLECT or the Filter:

    =JOIN(data, ", ")

    but the data portion is replaced with COLLECT:

    =JOIN(COLLECT(filter data), ", ")


    Try copy/pasting the exact formula that @sharkasits listed above:

    =JOIN(COLLECT({Source}, {Site Name}, [Site Name]@row), ", ")

    Notice that there's a ) right after the [Site Name]@row reference. This closes out the COLLECT part of the formula before you go into the comma, ", " which is part of the JOIN function.

    Cheers,

    Genevieve

  • OH MY GOODNESS it worked! Thank you for your guidance and patience!! The smallest change in the formula completely changes it, thank you so much again.

  • Genevieve P.
    Genevieve P. Employee Admin

    Haha no problem at all! 🙂 I'm glad you were able to figure it out. Formulas can be sneaky like that!

  • sharkasits
    sharkasits ✭✭✭✭✭

    @laura.sandoval Just coming back to this... glad it worked for you. The closed parenthesis in the wrong place gets me all the time too. @Genevieve P. thank you for adding clarity to the formula!! 😀

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!