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))))