VLOOKUP help formula

laura.sandoval

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?

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), ", ")

laura.sandoval

=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.

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

laura.sandoval

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.

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

laura.sandoval

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.

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

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!! 😀