Excel, return unique values values from an array, but don't return blank values

Question

Here's my formula: =TEXTJOIN(", ",TRUE,UNIQUE(IF(A2=Sheet1!$E:$E,Sheet1!$L:$L,"")))

Here's my result: 0, AOI-1, AOI-2

So far it's working to return unique values in Sheet1!$L:$L; but I don't want it to return a 0 for the blank values in Sheet1!$L:$L.

All rows in Sheet1!$E:$E have values that are not blank, my values to return are sometimes blank, but I don't want to see those returned.

Another way to say it is, if VALUE_IS_TRUE is blank, don't return it.

Jody Highroller · Accepted Answer · 2021-09-10 00:11:20Z

1

Leverage the FILTER function, in which you can specify the value to be returned if blank

=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Sheet1!$L:$L,A2=Sheet1!$E:$E,"")))

The parameters for FILTER are

=FILTER(array, include, [if blank])

So we are returning an array from column L if the value in column E matches A2, and if column L is blank it will return nothing for that value.

answered Sep 10, 2021 at 0:11

Jody Highroller

1,0396 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

That did it! I tried filter, but it was backwards