Need help returning multiple values to one cell based on a two criteria

[jmw24]

I have multiple properties and at each property there are multiple tenants. I am trying to show all the tenants that have that date to show up in the one cell. With this formula I can only get one tenant to show up, not both. How do I get both tenants to show up in the cell.

Sheet 1

Property	9/30/2022	10/31/2022
Property 1	=iferror(xlookup($A2&B$1,'Sheet2'!$A$2:$A$7&'Sheet2'!$C$2:$C$7,'Sheet2'$B$2:$B$7),0)
Property 2
Property 3

Sheet 2

Property	Tenant	Date
Property 1	Tenant 1	9/30/2022
Property 1	Tenant 2	9/30/2022
Property 2	Tenant 3	10/31/2022
Property 2	Tenant 4	9/30/2022
Property 3	Tenant 5	10/31/2022
Property 3	Tenant 6	10/31/2022

[#2]

@jmw24 

The FILTER function is what you need, and because it yields what's called a dynamic array, you need to put the different properties side by side, rather than vertically. I've attached one sample of how that could look. If you change the dates or property numbers in the orange background cells, the results will change to reflect your criteria.

The table on Sheet2 is a dynamic table, so you could add rows and see that the filter function on Sheet1 keeps up with you.

Here too is a good resource for learning about the FILTER function. (Oh, you will need a current version of Excel)

[#3]

@jmw24 The final piece you're looking for is the TextJoin which, when combined with the filter function already mentioned, will return multiple values in a single cell as shown.    

=IFERROR(TEXTJOIN(" / ",,FILTER(Table3[Tenant],(Table3[Property]=$A2)*(Table3[Date]=B$1))),"")

This and formula requires office 365 in order to work properly.  

Hope this helps. 

Dexter