Unique Distinct Lists Quirk?

[Jnrrpg11]

Hello Everyone,

Im trying too understand whats happening with a small oddity when creating a Unique Distinct list.

So in Column 'C' I have list of names with duplicates and in the column 'G' I have a Unique Distinct List created using the red formula at the top.
Ive extended the Array beyond the number of names necessary so I know that if another name appeared in range C5:C24 it would populate below the first UDL.
However when I extend the range to take into account an increasing Name list in future (UDL2/Green formula), I get a 0 between the last name the first #N/A .
Im not super clued in on Unique Distinct Lists but it seems like a strange quirk and I know I can use an IFNA to remove the #N/A but i cannot get rid of the 0.
Can one any explain whats happening here and potentially suggest a solution?

Many Thanks.

 

[Scott T]

If we evaluate the lookup array of the match we get

=INDEX($C$5:$C$28,MATCH(0,{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;0;0;0;0},0))

The zeros at the end are the blank cells and since your are matching on zero it returns as row number.

If you put a space in the empty cells in column C the it returns as blank instead of zero.

Or you could check if it returns zero and make it return ""

=IF(INDEX($C$5:$C$28,MATCH(0,COUNTIF($J$4:J4,$C$5:$C$28),0))=0,"",INDEX($C$5:$C$28,MATCH(0,COUNTIF($J$4:J4,$C$5:$C$28),0)))

 

[Jnrrpg11]

Hey Scott,
Ive looked online for weeks and never managed to find a solution to this.

Thanks for the reply, works perfectly.


Kind Regards