SOLVED
Home

Multiple Answers for Unique Value

%3CLINGO-SUB%20id%3D%22lingo-sub-888570%22%20slang%3D%22en-US%22%3EMultiple%20Answers%20for%20Unique%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888570%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%3C%2FP%3E%3CP%3EI%20have%20multiple%20skill%20assigned%20for%20every%20extension%20in%20the%20contact%20center.%20Need%20assistance%20on%20formula%20to%20have%20all%20the%20extensions%20to%20which%20the%20skills%20has%20been%20mapped.%3C%2FP%3E%3CP%3EAttached%20workbook%20with%20Master%20Sheet%20%26amp%3B%20Answer%20Sheet.%3C%2FP%3E%3CP%3EExample%20%3A%20The%20master%20sheet%20has%20Skill%203918%20assigned%20to%20various%20Extensions.%20How%20to%20get%20all%20those%20extension%20number%20mapped%20to%20skill%203918%20and%20so%20forth%20for%20remaining%20skills%20as%20mentioned%20in%20the%20answer%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20expert.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-888570%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-888596%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Answers%20for%20Unique%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888596%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409085%22%20target%3D%22_blank%22%3E%40sonbarse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20Array%20Formula%20which%20requires%20confirmation%20with%20Ctrl%2BShift%2BEnter%20instead%20of%20Enter%20alone.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20Answer%20Sheet%2C%3C%2FP%3E%3CP%3EIn%20B2%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(Master!%24A%242%3A%24A%2425%2CSMALL(IF(Master!%24B%242%3A%24D%2425%3D%24A2%2CROW(Master!%24A%242%3A%24A%2425)-ROW(Master!%24A%242)%2B1)%2CCOLUMNS(%24B2%3AB2)))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20then%20copy%20it%20across%20and%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-888630%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Answers%20for%20Unique%20Value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-888630%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409085%22%20target%3D%22_blank%22%3E%40sonbarse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20variant%20is%20with%20Power%20Query%2C%20steps%20as%20in%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E
sonbarse
Visitor

Hi.

I have multiple skill assigned for every extension in the contact center. Need assistance on formula to have all the extensions to which the skills has been mapped.

Attached workbook with Master Sheet & Answer Sheet.

Example : The master sheet has Skill 3918 assigned to various Extensions. How to get all those extension number mapped to skill 3918 and so forth for remaining skills as mentioned in the answer sheet.

 

Thanks in advance for expert.

2 Replies

@sonbarse 

Please try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

 

On Answer Sheet,

In B2

=IFERROR(INDEX(Master!$A$2:$A$25,SMALL(IF(Master!$B$2:$D$25=$A2,ROW(Master!$A$2:$A$25)-ROW(Master!$A$2)+1),COLUMNS($B2:B2))),"")

and then copy it across and down.

 

 

Solution

@sonbarse 

Another variant is with Power Query, steps as in attached

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies