Is it possible to combine vlookup, join and collect AND reference an external sheet?

Brian Barscht

I'm trying to group information together from a second smartsheet, but the information requires a vlookup so a typical join/collect won't work.

In the first smartsheet I have a company ID in each row that I want to look up, and in the second smartsheet there are hundreds of rows that contain company IDs in the first column and other data in the other columns. There are often multiple matches for the company IDs, but I don't want to pull the IDs, I want to pull the 5th column to the right from the column containing the matching IDs. Then I want to concatenate the multiple matches together in one cell in the first sheet.

Thanks in advance if anyone can help me solve this!

L_123

I don't really understand why you need a vlookup for this, collect has it's own filtering ability. from what you are describing it should just be a join(collect. should look something like:

 

=join(collect([5th Column Sheet Reference],[ID Sheet Reference],[ID to look up]@row),",")

Paul Newcome

One slight adjustment to the above formula...

 

=join(collect({5th Column Sheet Reference},{ID Sheet Reference},[ID to look up]@row),",")

 

Cross sheet references will not have square brackets.

Terri-Lynn Morrison

I am having an issue getting this formula to work for me.

Sheet "1.Master" where I want the consolidated information to go contains [Site No] column (unique identifier/primary column) and a column called "Zoning" among 50 some other columns. [Site No] column is the first column in this sheet.

Sheet "SitePolygons_Zoning" contains Site No (not unique) {SitePolygons_Zoning Range 2}, and Zoning{SitePolygons_Zoning Range 1}

Formula based on above is:

=JOIN(COLLECT({SitePolygons_Zoning Range 1}, {SitePolygons_Zoning Range 2}, [Site No]1), ",")

When I type in this formula into the column "Zoning" on sheet "1.Master" it says it is calculating, but nothing appears.

Genevieve P.

Hi @Terri-Lynn Morrison

I tested your formula and it worked on my sheet as expected, so your syntax is correct. I would try logging out/in again to see if that helps, or accessing the sheet from a different browser. Try drag-filling it into a different cell, too, or creating a copy of the sheet and testing there.

I hope this helps!

Cheers,

Genevieve