Filter and Textjoin to return multiple results in same cell

[CMScons]

I am trying to arrange data from three columns into a matrix which uses the unique values of two of those columns ("Department" and "Grade") to identify all of the values of that third column ("Position Title") which meet the criteria, and to display them all into the corresponding cell. I have tried XLOOKUP with some success, when only one match is made, but I run into problems when I instead try to use FILTER, I've also tried MATCH.

This formula, returns too much information (results highlighted in yellow, below). As only one filter is applied, I expected it to return all titles within the "Community Enrichment" department, based on cell M1.
=TEXTJOIN(", ",TRUE,FILTER(Table1[[Position Title]:[Grade]],(Table1[Department]=M$1),""))

When I add a second criteria for the "Include", it returns an #N/A error. I expected it to return all titles within the "Community Enrichment" department that are considered "Grade 14", based on cell G11.
=TEXTJOIN(", ",TRUE,FILTER(Table1[[Position Title]:[Grade]],(Table1[Department]=M$1)*(Table1[Grade]=$G11),""))

I have not worked with any of these functions until this specific project, so any help or direction is appreciated.

Thank you!

 

[Fluff]

Hi & welcome to MrExcel.
If you just want the position title it should be

=TEXTJOIN(", ",TRUE,FILTER(Table1[Position Title],(Table1[Department]=M$1)*(Table1[Grade]=$G11),""))

If you are getting #N/A errors, make sure that error does not exist in the table.

 

[CMScons]

Hi & welcome to MrExcel.
If you just want the position title it should be

=TEXTJOIN(", ",TRUE,FILTER(Table1[Position Title],(Table1[Department]=M$1)*(Table1[Grade]=$G11),""))

If you are getting #N/A errors, make sure that error does not exist in the table.

Thank you for the quick response. I used your formula with limited success. As provided, I still get an #N/A error. However, when I remove the second "Include" criteria (Table1[Grade]=$G11), I do get titles only, so that part is resolved. I want to understand your statement "make sure that error does not exist in the table": There are a couple of grades in the whole "Grade" array which include an #N/A error, but none within the selected Department. Are you saying that if the error exists anywhere in the table, it will error for any response which references that array?

 

[Fluff]

You are getting the error because you have #N/A errors in the Grade column. You need to remove them.

 

[CMScons]

Thank you. After overwriting the errors, it does work perfectly. I made the arrays absolute and can drag the formula across the full matrix and it populates as hoped. Thank you so much.

=TEXTJOIN(", ",TRUE,FILTER(Table1[[Position Title]:[Position Title]],(Table1[[Department]:[Department]]=M$1)*(Table1[[Grade]:[Grade]]=$G14),""))

 

[Fluff]

You're welcome & thanks for the feedback.