TEXTJOIN and IF and MATCH

%3CLINGO-SUB%20id%3D%22lingo-sub-2819271%22%20slang%3D%22en-US%22%3ETEXTJOIN%20and%20IF%20and%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2819271%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20a%20little%20bit%20of%20background%2C%20I%20am%20using%20the%20TEXTJOIN%20and%20IF%20statement%20to%20pull%20multiple%20values%20from%20one%20tab%20and%20pull%20them%20into%20one%20cell%20on%20a%20tab.%20This%20works%20and%20the%20values%20are%20pulled%20in%20the%20problem%20is%20then%20the%20output%20has%20duplicate%20values%20that%20I%20want%20to%20filter%20out%20using%20the%20same%20formula.%20This%20is%20the%20formula%20I%20am%20using%26nbsp%3B%3DTEXTJOIN(%22%2C%22%2CTRUE%2CIF('SC%20Data'!B2%3A%24B%243856%3D'Commodity%20Tracker'!A2%2C'SC%20Data'!%24D%242%3A%24D%243856%2C%22%22))%20This%20is%20the%20output%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%221790%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%221790%22%3E2D-05021461%2C2D-05021461%2C2D-05021461%2C2D-05021461%2C2D-05021461%2C2D-05021461%2C2D-05021461%2C2D-05021461%2C2D-05021461%2C2D-05021461%2C2D-05021461%2C2D-05021461%2C2D-05021461%2C2D-05021461%2C2D-05021461%2C2D-05021461%2C2D-05021461%2C2D-05021461%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EThe%20version%20of%20excel%20I%20am%20using%20does%20not%20support%20UNIQUE%20function%20unfortunately.%20I%20have%20seen%20people%20using%20the%20MATCH%20function%20but%20for%20some%20reason%20I%20just%20cant%20wrap%20my%20head%20around%20it.%20Thanks%20a%20ton!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2819271%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2819332%22%20slang%3D%22en-US%22%3ERe%3A%20TEXTJOIN%20and%20IF%20and%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2819332%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1177553%22%20target%3D%22_blank%22%3E%40Lopezr2307%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20this%20thread%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fremoving-duplicates-when-using-textjoin%2Fm-p%2F188950%22%20target%3D%22_blank%22%3ERemoving%20duplicates%20when%20using%20TEXTJOIN%20-%20Microsoft%20Tech%20Community%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2819362%22%20slang%3D%22en-US%22%3ERe%3A%20TEXTJOIN%20and%20IF%20and%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2819362%22%20slang%3D%22en-US%22%3EThat%20is%20the%20thread%20I%20have%20been%20following%20and%20the%20video%20that%20I%20was%20using%2C%20the%20problem%20is%20that%20it%20does%20not%20incorporate%20the%20lookup%20function%20as%20well..%20I%20need%20to%20be%20able%20to%20perform%20the%20if%20a%20%3D%20b%20then%20give%20me%20c%2C%20then%20sort%20through%20all%20the%20c's%20and%20only%20return%20unique%20values%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2819572%22%20slang%3D%22en-US%22%3ERe%3A%20TEXTJOIN%20and%20IF%20and%20MATCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2819572%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1177553%22%20target%3D%22_blank%22%3E%40Lopezr2307%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20try%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20605px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F315691iDBA92248EB7CF614%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%20array%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DTEXTJOIN(%22%2C%20%22%2C%201%2C%0A%20%20IF(%20COUNTIF(A2%2CB2%3AB28)*%0A%20%20%20%20%20(%20MATCH(C2%3AC28%2CC2%3AC28%2C0)%20%3D%20(ROW(C2%3AC28)-ROW(%24C%241))%20)%2C%0A%20%20%20%20%20INDEX(%20C2%3AC28%2C%20ROW(C2%3AC28)-ROW(%24C%241)%20)%2C%0A%20%20%20%22%22)%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

So a little bit of background, I am using the TEXTJOIN and IF statement to pull multiple values from one tab and pull them into one cell on a tab. This works and the values are pulled in the problem is then the output has duplicate values that I want to filter out using the same formula. This is the formula I am using =TEXTJOIN(",",TRUE,IF('SC Data'!B2:$B$3856='Commodity Tracker'!A2,'SC Data'!$D$2:$D$3856,"")) This is the output 

2D-05021461,2D-05021461,2D-05021461,2D-05021461,2D-05021461,2D-05021461,2D-05021461,2D-05021461,2D-05021461,2D-05021461,2D-05021461,2D-05021461,2D-05021461,2D-05021461,2D-05021461,2D-05021461,2D-05021461,2D-05021461

The version of excel I am using does not support UNIQUE function unfortunately. I have seen people using the MATCH function but for some reason I just cant wrap my head around it. Thanks a ton! 

3 Replies
That is the thread I have been following and the video that I was using, the problem is that it does not incorporate the lookup function as well.. I need to be able to perform the if a = b then give me c, then sort through all the c's and only return unique values

@Lopezr2307 

You may try

image.png

with array formula

=TEXTJOIN(", ", 1,
  IF( COUNTIF(A2,B2:B28)*
     ( MATCH(C2:C28,C2:C28,0) = (ROW(C2:C28)-ROW($C$1)) ),
     INDEX( C2:C28, ROW(C2:C28)-ROW($C$1) ),
   "") )