Forum Discussion
Get a list from multiple cells returned into a single cell based on contents of two other cells
Hello there, I hope someone is able to help. I have a problem I am trying to solve involving two sheets. I have attached screenshots, which I hope will be useful in understanding what I am trying to...
- I didn't evaluate your formula to determine why it returns what it does but will suggest a different approach. Use FILTER so something like:
=TEXTJOIN(", ",, FILTER( Property_Table[Room / Flat], (Property_Table[Surname]="Void")*(Property_Table[Service] = [@Service]),""))
the above uses structured formatting assuming you defined each as a table (home -> 'format as table') and named the table on the property tab 'Property_Table'. The benefits of formatting as table and naming the table is that the structured format is human readable and you can SEE what it is looking up instead of just seeing columns and rows and have to go figure it out. Also as the table grows the range will grow automatically and you don't have to go back and check and see if you need to adjust the formula to include more and you don't have to include the entire column just in case the data grows.
I didn't evaluate your formula to determine why it returns what it does but will suggest a different approach. Use FILTER so something like:
=TEXTJOIN(", ",, FILTER( Property_Table[Room / Flat], (Property_Table[Surname]="Void")*(Property_Table[Service] = [@Service]),""))
the above uses structured formatting assuming you defined each as a table (home -> 'format as table') and named the table on the property tab 'Property_Table'. The benefits of formatting as table and naming the table is that the structured format is human readable and you can SEE what it is looking up instead of just seeing columns and rows and have to go figure it out. Also as the table grows the range will grow automatically and you don't have to go back and check and see if you need to adjust the formula to include more and you don't have to include the entire column just in case the data grows.
=TEXTJOIN(", ",, FILTER( Property_Table[Room / Flat], (Property_Table[Surname]="Void")*(Property_Table[Service] = [@Service]),""))
the above uses structured formatting assuming you defined each as a table (home -> 'format as table') and named the table on the property tab 'Property_Table'. The benefits of formatting as table and naming the table is that the structured format is human readable and you can SEE what it is looking up instead of just seeing columns and rows and have to go figure it out. Also as the table grows the range will grow automatically and you don't have to go back and check and see if you need to adjust the formula to include more and you don't have to include the entire column just in case the data grows.
