EXCEL TEXTJOIN with IF and UNIQUE IN O365

user_13432807 0
Viewed 181

Using Excel O365.
My formula: {=TEXTJOIN(", ",TRUE,UNIQUE(IF('Sheet1'!C$2:F$36=$B2,'Sheet1'!$A$2:$A$36,"")))}

My 4 Objectives:

  1. When a cell in an array ('Sheet1'!C$2:F$36) equals a cell in my current row ($B2) return the corresponding cell in ('Sheet1'!$A$2:$A$36).
  2. Separate all results with a comma and a space after the comma
  3. Ignore Blank Cells.
  4. Remove Duplicates.

My Result: Objective 1, 2 and 3 works Perfectly. However, I cannot reduce to Unique values.
Important note: If I change to look at a single column, change my lookup array to ('Sheet1'!C$2:C$36) then it works perfectly and returns only unique values.

How can I return unique values when lookup is an array?

office365 excel-formula excel
1 Answers

You could try:

=TEXTJOIN(", ",,FILTER(A2:A36,MMULT(--(C2:F36=B2),SEQUENCE(4,,,0)),"No Hits"))
user_97581940
Related