SOLVED

maybe wrong function

%3CLINGO-SUB%20id%3D%22lingo-sub-1568649%22%20slang%3D%22en-US%22%3Emaybe%20wrong%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1568649%22%20slang%3D%22en-US%22%3E%3CP%3EI%20created%20a%20form%20to%20use%20as%20a%20check%20off%20items%20completed.%20A%20lot%20of%20the%20items%20will%20have%20more%20than%201%20response%2C%20I%20am%20trying%20to%20use%20a%20formula%20to%20place%20multiple%20names%20from%20the%20responses%20to%20one%20cell%20on%20the%20master%20check%20off%20sheet.%20Using%20the%20Index%2C%20match%2C%20formula%20I%20can%20only%20get%20one%20name%20cause%20I%20am%20guessing%20it%20based%20off%20the%20intersection%20of%20row%20and%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1568649%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570664%22%20slang%3D%22en-US%22%3ERe%3A%20maybe%20wrong%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570664%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750518%22%20target%3D%22_blank%22%3E%40lovingizneverez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EINDEX%2FMATCH%2C%20VLOOKUP%2C%20etc%20return%20only%20first%20found%20value.%20To%20return%20multiple%20values%20FILTER%20or%20other%20functions%20could%20work.%20Better%20to%20have%20a%20sample%20to%20be%20more%20concrete.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570891%22%20slang%3D%22en-US%22%3ERe%3A%20maybe%20wrong%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570891%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20response.%20Using%20the%20formula%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF('Post%20Ops%20Data'!%24F%242%3A%24F%244%3D%22Completed%201.0%20hr%22%2C'Post%20Ops%20Data'!%24E%3A%242%3A%24E%244%2C%22NA%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EI%20seem%20to%20get%20the%20results%20that%20I%20am%20looking%20for%20except%20for%20the%20fact%20that%20the%20formula%20is%20in%20A7%20and%20the%20results%20returns%20in%20A7%2CA8%2CA9%2C%20and%20I%20need%20them%20to%20be%20for%20example%20A7%20should%20display%20Joe%2C%20NA%2C%20Rickey%26nbsp%3B%20instead%20of%20A7%20Joe%20A8%20NA%20A9%20Rickey.%20Any%20help%20would%20be%20greatly%20appreciated%26nbsp%3B%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%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570898%22%20slang%3D%22en-US%22%3ERe%3A%20maybe%20wrong%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570898%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750518%22%20target%3D%22_blank%22%3E%40lovingizneverez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%20you%20need%20to%20wrap%20it%20with%20TEXTJOIN%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTEXTJOIN(%22%2C%20%22%2C1%2CIF('Post%20Ops%20Data'!%24F%242%3A%24F%244%3D%22Completed%201.0%20hr%22%2C'Post%20Ops%20Data'!%24E%3A%242%3A%24E%244%2C%22NA%22))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1570924%22%20slang%3D%22en-US%22%3ERe%3A%20maybe%20wrong%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1570924%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much%20that%20was%20exactly%20what%20I%20needed.%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%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1571122%22%20slang%3D%22en-US%22%3ERe%3A%20maybe%20wrong%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1571122%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750518%22%20target%3D%22_blank%22%3E%40lovingizneverez%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I created a form to use as a check off items completed. A lot of the items will have more than 1 response, I am trying to use a formula to place multiple names from the responses to one cell on the master check off sheet. Using the Index, match, formula I can only get one name cause I am guessing it based off the intersection of row and column.

5 Replies

@lovingizneverez 

INDEX/MATCH, VLOOKUP, etc return only first found value. To return multiple values FILTER or other functions could work. Better to have a sample to be more concrete.

Thank you for your response. Using the formula 

=IF('Post Ops Data'!$F$2:$F$4="Completed 1.0 hr",'Post Ops Data'!$E:$2:$E$4,"NA")

I seem to get the results that I am looking for except for the fact that the formula is in A7 and the results returns in A7,A8,A9, and I need them to be for example A7 should display Joe, NA, Rickey  instead of A7 Joe A8 NA A9 Rickey. Any help would be greatly appreciated @Sergei Baklan 

Best Response confirmed by lovingizneverez (Occasional Contributor)
Solution

@lovingizneverez 

I guess you need to wrap it with TEXTJOIN like

=TEXTJOIN(", ",1,IF('Post Ops Data'!$F$2:$F$4="Completed 1.0 hr",'Post Ops Data'!$E:$2:$E$4,"NA"))

 

Thank you so much that was exactly what I needed.@Sergei Baklan 

@lovingizneverez , you are welcome