LOOKUP function not working for certain value

[rickytb]

Dear community,

I am struggling with a formula that I have in an excel file. I am using the following formula to retrieve unique values from a list. There is always a maximum of 3 unique fields, that is why I use only cells A1:A3, but it could in principle go on:
Cell A1: ='Input'!D2
Cell A2: =LOOKUP(2;1/('Input'!D2:D100=A1);'Input'!D3:D100)
Cell A3: =LOOKUP(2;1/('Input'!D2:D100=A2);'Input'!D3:D100)

The strange thing is.. This formula seems to work perfectly for most values on the input sheet, except for one. Example:
Regular
Regular
Regular
Test
Test
Example
Example

This will result A1=Regular, A2=Test and A3=Example

The strange thing, as said, it works with almost all values, except for the value ZWK. The following example only gives ZWK in the first field and stops thereafter.
ZWK
ZWK
ZWK
Regular
Regular
Test

This will result A1=ZWK, while A2 and A3 remain empty. I tried many things, values like ZWO, ZWP are all working fine. The only issue is with ZWK.
Does any of you have a clue why this formula does not work with this specific value?

Thanks in advance for your support.

Regards,
Ricky

 

[earthworm]

Might be possible there are extra spaced at the end of text . Use trim function

 

[rickytb]

Might be possible there are extra spaced at the end of text . Use trim function

Done, but didn't resolve the issue.. Could you try to setup the same formulas in Excel and see if it works for you?

 

[earthworm]

Please upload the sheet.

 

[rickytb]

Please upload the sheet.

Sorry, I don't know how to upload the sheet. However, I found why it is not working, but do not yet know how to resolve it.
It goes wrong if the value comes back later in the sheet. Example:
Regular
Regular
Regular
Test
Test
Example
Example
Regular

This does not work, while it will work if I remove the bottom "Regular".

Would you know how to resolve this?

 

[rickytb]

If you have another solution that is also fine of course. Basically, I want to have the list from the other sheet without duplicates and no manual action.
Thanks a lot in advance for your support.

 

[rickytb]

I found a solution that seems to work!

=IFERROR(INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$2:$A$100)+($A$2:$A$100=""),),0)),"")

 

[earthworm]

=INDEX($C$2:$C$9,SMALL(IF(FREQUENCY(IF($C$2:$C$9<>"",MATCH($C$2:$C$9,$C$2:$C$9,0)),ROW($C$2:$C$9)-ROW($C$2)+1),ROW($C$2:$C$9)-ROW($C$2)+1),ROWS(E$2:E2)))