I am trying to modify this. Sheet 1 hasName and category column and sheet 2 has Name and category names as column i want to display as following way i tried Vlookup and match however no luck.

CodePudding user response：

You may try-

=INDEX($G$3:$J$3,1,MATCH("y",INDEX(TRANSPOSE($G$4:$J$8),,MATCH(A2,$F$4:$F$8,0)),0))

Edit: See below updated formula

=FILTER($G$3:$J$3,MMULT(SEQUENCE(1,ROWS($G$4:$J$8),1,0),($G$4:$J$8="y")*($F$4:$F$8=A2)))

If you want to show result in same cell by comma separated then try-

=TEXTJOIN(", ",TRUE, FILTER($G$3:$J$3,MMULT(SEQUENCE(1,ROWS($G$4:$J$8),1,0),($G$4:$J$8="y")*($F$4:$F$8=A2))))