Home

Help with functions

%3CLINGO-SUB%20id%3D%22lingo-sub-529045%22%20slang%3D%22en-US%22%3EHelp%20with%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-529045%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20attached%20an%20example%20of%20my%20SS.%20I%20am%20looking%20at%20a%20way%20to%20input%20a%20CONFIG%20item%20listed%20in%20the%20left-hand%20column%20and%20have%20it%20list%20all%20of%20the%20users%20who%20support%20that%20configuration.%26nbsp%3B%20So%20if%20I%20were%20to%20input%20AMB%20in%20a%20cell%20it%20would%20look%20at%20the%20row%20with%20AMB%20in%20it%20and%20return%20all%20the%20users%20in%20the%20top%20row%20who%20have%20an%20X%20in%20the%20AMB%20row.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-529045%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-529332%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-529332%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F332969%22%20target%3D%22_blank%22%3E%40MJShepherd%3C%2FA%3E%26nbsp%3B%2C%20if%20TEXTJOIN%20is%20avilable%20for%20your%20version%20of%20Excel%2C%20you%20may%20use%20it%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(TEXTJOIN(%22%2C%22%2CTRUE%2CIF(INDEX(Table1%5B%5BJB%5D%3A%5BDP%5D%5D%2CMATCH(A2%2CTRIM(Table1%5BCONFIG%5D)%2C0)%2C0)%3D%22X%22%2CTable1%5B%5B%23Headers%5D%2C%5BJB%5D%3A%5BDP%5D%5D%2C%22%22))%2C%22%22)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-645288%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-645288%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20Thank%20you%20this%20works%20very%20well%20for%20me%2C%20however%2C%20when%20I%20sent%20it%20to%20my%20team%20the%20function%20did%20not%20work%20for%20them.%20I%20am%20missing%20something.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-645356%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-645356%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F348488%22%20target%3D%22_blank%22%3E%40MJShepherd1%3C%2FA%3E%26nbsp%3B%2C%20perhaps%20they%20are%20on%20Excel%20versions%20for%20which%20function%20is%20not%20available.%20You%20may%20check%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Ftextjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Ftextjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c%3C%2FA%3E%20where%20it%20shall%20be.%3C%2FP%3E%3C%2FLINGO-BODY%3E
MJShepherd
Occasional Visitor

I have attached an example of my SS. I am looking at a way to input a CONFIG item listed in the left-hand column and have it list all of the users who support that configuration.  So if I were to input AMB in a cell it would look at the row with AMB in it and return all the users in the top row who have an X in the AMB row. 

3 Replies

@MJShepherd , if TEXTJOIN is avilable for your version of Excel, you may use it

=IFERROR(TEXTJOIN(",",TRUE,IF(INDEX(Table1[[JB]:[DP]],MATCH(A2,TRIM(Table1[CONFIG]),0),0)="X",Table1[[#Headers],[JB]:[DP]],"")),"")

 

@Sergei Baklan  Thank you this works very well for me, however, when I sent it to my team the function did not work for them. I am missing something. 

@MJShepherd1 , perhaps they are on Excel versions for which function is not available. You may check here https://support.office.com/en-us/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c where it shall be.