Pulling Values from Multiple Columns Based on Same Unique Identifier Not Working

ANGELAPRIOR

Hi Smartsheet Community,

I have a source sheet where I am trying to pull values based on a unique identifier in the target sheet, but since the unique identifier is the same, it is only pulling the value from the first column.

To go into more depth:

Sheet 1 - Questions/Comments Sheet

User posts a question or a comment on a form, and a Question ID is auto-populated within this sheet.

Sheet 2 - Answers Sheet

Users see the question or comment on a dashboard, and click a link to be directed to a form that, through a string query, allows the user to respond to that specific Question ID.

Answers Sheet has these 2 columns (among others): Answer 1 and Answer 2.

Based on logics set up in the Answers Sheet Form, the user's response will either fall into Answer 1 or Answer 2.

The problem I am facing is that Answer 1 data is pulling in just fine to the Questions/Comments Sheet via an index/match formula, however since the unique identifier (Question ID) is the same for both Answer 1 and Answer 2, it is only pulling the first value - in this case Answer 1.

Here is the formula I am currently using: =IFERROR(INDEX({Community Answers Ans1}, MATCH(QuestionID@row, {Community Answers Entire Range}, 0)), "")

I am thinking this needs to be changed to a JOIN/COLLECT formula, but I am unsure how to proceed; I keep running into errors when trying to set this up. Any help would be appreciated!

Paul McGuinness

Hi @ANGELAPRIOR

If there will only be 1 answer in one of the community answer columns then you can just pull them together and only the answer will be displayed.

The formula below should work, but i did assume the name of the second community answers range, so you may need to amend that otherwise.

=IFERROR(INDEX({Community Answers Ans1}, MATCH(QuestionID@row, {Community Answers Entire Range}, 0))+INDEX({Community Answers Ans2}, MATCH(QuestionID@row, {Community Answers Entire Range}, 0)),"")

Hope that helps

Thanks

Paul

ANGELAPRIOR

Thanks for the reply, Paul. I actually figured out something different that will easily help me to pull all responses in to my target sheet using the unique identifier: =JOIN([Answer 1]@row:[Answer 5]@row, "; ")

However, now I am encountering semi-colons being displayed on their own (see screenshot below). Any ideas on how I can ensure those do not appear unless Answer 3, 4, 5, etc. is populated?

ANGELAPRIOR

Nevermind Paul, figured that one out too! Using CHAR(10) as my "delimiter" worked.

ANGELAPRIOR

So, to help out anyone in the future, my final formula ended up looking like this:

Sheet 1 - Source Sheet

=JOIN([Answer 1]@row:[Answer 5]@row, CHAR(10))

Sheet 2 - Target Sheet

=IFERROR(INDEX({Community Answers Responses}, MATCH(QuestionID@row, {Community Answers QuestionID}, 0)), "")