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

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!

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.