• 517K Members
• 6,194 Online
• 615K Conversations
SOLVED

New Contributor

# Multiple Answers for Unique Value

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.

2 Replies

# Re: Multiple Answers for Unique Value

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

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

# Re: Multiple Answers for Unique Value

Another variant is with Power Query, steps as in attached

Related Conversations
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
if then formula help
BMARSH99 in Excel on
1 Replies
Q&A examples
Dean Gross in Microsoft Search on
0 Replies