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 achieve here.
Take a look at the formula in H7 on the Void Tracker. It's nearly there, but it isn't quite working. What I'm trying to get it to do is to look at column B in the property list and find all the properties listed as Void for all the cells in Column D with the address "99 The Avenue" in column D and return all the relevant rooms in column E. So, what I want it to do, is return me a list of "Room 2 Room 3 Room 4" in Cell H7 of the void tracker, as those rooms are listed as Void in column B under that address. It should not return Room 1 as that is not listed as Void.
However, it isn't doing what I want, as you can see. The cell value that it is retuning is E14 of the Property list, but it's just retuning that cell value four times.
If anyone can help, I'd greatly appreciate it.
Many thanks.
- 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.
