Cross reference a sheet and return multiple values based on selection

Catherine Shea

looking for some formula help .....i have a working sheet (#1) that when selecting one or multiple [Suggested Remedy Code]@row that it will search my reference sheet (#2) for the correlating {Result Code} and populate with all the {Description} associate with the respective codes in my sheet 1 column [Corrective Action] ---i have been playing with index, collect and has and simply stumped....

Devin Lee

Give this a try. You'll need to replace the column name with the reference name where applicable.

=JOIN(COLLECT(Description:Description, [Result Code]:[Result Code], HAS([Suggested Remedy Code]@row, @cell)), " - ")

Devin Lee

Give this a try. You'll need to replace the column name with the reference name where applicable.

=JOIN(COLLECT(Description:Description, [Result Code]:[Result Code], HAS([Suggested Remedy Code]@row, @cell)), " - ")

Catherine Shea

Just wanted so say "THANK YOU!" I am but a novice and tried to figure this out on my own. Do you have any suggestions on how I can level up with any resources/authors on how to learn and build writing formulas? Just to let you know I had to make a minor adjustment and it worked. This is what I had to ultimately tweak: =JOIN(COLLECT({Corrective Description}, {Result Code}, HAS([Suggested Remedy Code]@row, @cell)), "-")

Devin Lee

@Catherine Shea

Review the function list and the examples each one has. https://help.smartsheet.com/functions

Lurk the discussions. Most of the questions people come here with questions that have already been answered somewhere in the past. Smartsheet University is a good starting point that I recommend everyone at least takes a look at even if you don't want do all the lessons. A lot of the knowledge around Smartsheet just comes down to knowing whether or not Smartsheet is capable of accomplishing what you are trying to do so a quick review can go a long way. Lastly, helping people in these discussions is a great way to expand your understanding. Even if you can't answer them it's good to try and then when someone else posts the answer you get to learn how they did it. Try not to just write out formulas in the discussion but actually build the sheet and see it work yourself.