Return list of competent skills by username

[johnmitch38]

On the attached, my data is in A1:D11

I want to be able to select a username in G1 and corresponding skills from A1:D11 that are marked with a Y for that user, then list below the user name in column G.

The attachment shows the desired results for user Joe Bloggs.

[CARACALLA]

G2=IFERROR(INDEX($A$2:$A$100,SMALL(IF($B$2:$D$100="Y",IF($B$1:$D$1=$G$1,ROW($A$2:$A$100)-ROW($A$2)+1)),ROWS($A$2:A2))),"")


Control+shift+enter

copy down

[alansidman]

Here is a VBA solution

Please Login or Register  to view this content.

[johnmitch38]

Many thanks both - I opted for the array formula, works perfectly.