Fix formula to get unique OP from multiple rows

[Liverpool100]

See attached workbook - trying to fix the formula in Column K which should deliver a unique output for each row while checking the OPs in the rows above also. The correct OP is shown in Col M.

[HansDouwe]

Why is in M8 C2 correct? I expected C1 from A8. See attached file.
C1 doesn't appear above in column M either.

If the intention is to find all unique OPs, use this formula:

Please Login or Register  to view this content.

[Glenn Kennedy]

Your profile is CLEARLY incorrect. Since you used FILTER, I assume you have O365. Please modify it NOW.

I suspect two of your expected answers are incorrect. Please check them (purple cells)

=LET(r,A2:J2,str,FILTERXML("<A><B>"&SUBSTITUTE(TEXTJOIN(",",,r),",","</B><B>")&"</B></A>","//B"),f,IF(ISNUMBER(MATCH(str,O$1:O1,0)),"",str),fl,FILTER(f,f<>""),INDEX(fl,1,))

copied down.

[HansDouwe]

This works also

Please try =A2 in cell N2
Please try in N3 and copy down:

Please Login or Register  to view this content.

@Liverpool100, You was very close with your own formula in column K

[Liverpool100]

Hi Glenn and Hans - both solutions work - much appreciated