INDEX/COLLECT Formula for gathering list of data

Hi, I am not sure if INDEX, MATCH or COLLECT will be the right things to use in this formula but I'm very much a beginner in using those functions.

On Sheet 1 I have a massive list of Restaurants (Column 1: Restaurant Name). Also on that sheet is Column 2: Division (For example Tampa Bay Restaurant is in the Southern Division).

On Sheet 2 I have a list of those Divisions in Column 1: Division. On Sheet 2 Column 2: Restaurants in Division, is where I would like a formula that looks at Sheet 1 and collects every restaurant name that is in the Division listed on Sheet 2 Column 1.

I've done a fair bit with INDEX/MATCH functions but nothing with COLLECT yet. Anyone have any ideas on how this could be done?

Find more posts tagged with

Sheets

Formulas

Comments

KayHays

Hi All, I figured out a JOIN/COLLECT Formula would be best for this. I have the formula working as follows with carriage return between each value (CHAR(10)).

SHEET 2 COLUMN 2 Restaurants in Division =JOIN(COLLECT({Sheet1RestaurantName}, {Sheet1Division}, Division@row), CHAR(10))

Andrée Starå

Hi @KayHays

I hope you're well and safe!

Excellent!

Glad you got it working!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Sign in to join the conversation!

Quick Links

Trending discussions

Logging separate data points of the same type of data for one row
Looking for a way to easily log and reference multiple data points for the same type of data (ex: successful phone contacts) that all show for one entry (by member name). Want this to be both easy to enter the information (ideally by interacting with the same row via Dynamic View), and easy to see data on the back end of…
Help With If Count If to Identify Unique Values in a Column
I'm trying to make a helper column to identify if a new/unique project code has been entered in a column so that I know when a new project has been created. I've been trying to set up this formula and plan to then set up an automation to be notified when "New Project Code" has been entered into a cell. It seems to be ok on…
Why does the parent task my % column show as a decimal value instead of a percentage?
Why does the parent task my % column show as a decimal value instead of a percentage? I checked the column and project settings, and they are identical to another smartsheet in which the values show up as a percentage.