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!
